SQL Import as text not Number

[Snailspace]

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I’m trying to pull some information in from a csv file with the following code.<o></o>

  SQL_OpenItems = "SELECT *" & _<o:p></o:p>
      " FROM Open_Invoices.csv" & _<o:p></o:p>
      " Where [Acc No]='" & strAccNo1 & "'" & _<o:p></o:p>
      " OR [Acc No]= '" & strAccNo2 & "'" & _<o:p></o:p>
      " ORDER BY [Acc No], [Invoice Date], [Doc No]"<o:p></o:p>

<o></o>
It looks like it is ‘guessing’ the data type as the majority of items in [Doc No] are numbers, however a few are text and these get omitted from the results.<o></o>
Is there a way to define the field as text when making the SQL statement?<o></o>
Cheers SP

 

[Ruddles]

Try:-

  SQL_OpenItems = "SELECT *[COLOR=red][B], CStr([Doc No]) As [Doc TxtNo][/B][/COLOR][COLOR=#000000]" & _
    " FROM Open_Invoices.csv" & _
      " Where [Acc No]='" & strAccNo1 & "'" & _
      " OR [Acc No]= '" & strAccNo2 & "'" & _
      " ORDER BY [Acc No], [Invoice Date], [Doc No]"

[/COLOR]
This will add an extra text field called Doc TxtNo to the output of the query, but I think the problem is that Excel is seeing all numerics and assuming it's a number.

If that doesn't work, try:-

  SQL_OpenItems = "SELECT *[COLOR=red][B], """" & [Doc No] & """" As [Doc TxtNo][/B][/COLOR][COLOR=#000000]" & _
    " FROM Open_Invoices.csv" & _
      " Where [Acc No]='" & strAccNo1 & "'" & _
      " OR [Acc No]= '" & strAccNo2 & "'" & _
      " ORDER BY [Acc No], [Invoice Date], [Doc No]"

[/COLOR]

 

[Richard Schollar]

Hi

What provider are you using to achieve this import?

 

[jimrward]

i generally use a schema file to declare my fields, data types and sizes, when using ADO import of CSV data

 

[Richard Schollar]

The other way would be to set IMEX=1 in the extended properties list for ADO, but this does depend on the import being done via ADo hence my question.

 

[Snailspace]

Thanks for the replies, apologies for not replying sooner, I needed a Friday night off.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Ruddles: I tried both your codes and it didn’t seem to make a change, it brought in the additional field OK for numbers, but not the text items.<o></o>
<o></o>
Rich: I think this is the info you queried, this is my first attempt at ADO so apologies if it’s not.
I think the IMEX=1 thing might be a bit over my head at the moment<o></o>
<o></o>

ConnectionString = _<o:p></o:p>
"Provider=Microsoft.Jet.OLEDB.4.0;" & _<o:p></o:p>
"Data Source=C:\Documents and Settings\HP_Administrator\My Documents\Work\ExcelAddins\Background Files\;" & _<o:p></o:p>
"Extended Properties=Text;"<o:p></o:p>
<o:p></o:p>
Call rsOpenItems.Open(SQL_OpenItems, ConnectionString, _<o:p></o:p>
CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, _<o:p></o:p>
CommandTypeEnum.adCmdText)<o:p></o:p>

<o></o>
<o></o>
<o></o>
<o></o>
Jim: After struggling to spell the file name correctly (doh), I’ve put the following into the Schema file, this seems to have defaulted all setting to text.
I guess its good practise to define each field’s data type.

[Open_invoice_temp.csv]<o:p></o:p>
ColNameHeader=true<o:p></o:p>
Format=CSVDelimited<o:p></o:p>
MaxScanRows=0<o:p></o:p>
"Doc No"= text width 20<o:p></o:p>

<o></o>
Thanks for the advice, I think I’m on the right track now.<o></o>
<o></o>SP<o></o>