Loading CSV File

[zhonghandle]

Hi,

I am trying to load the following csv file:

C1,C2,C3
1 ,M ,5
2 ,W ,1
3 ,M ,8C

Where C1, C2 and C3 are column names.

When I load the file using Microsoft.Jet.OLEDB.4.0, the table in SQL server looks like the following:

C1,C2,C3
1 ,M ,5
2 ,W ,1
3 ,M ,

Which 8C missing.

Can anyone tell me how to make the string value '8c' inserted?

Also, i tried to bulk load the CSV file in Query Analizer and I keep getting the error the source file not find even if the file is there. Is that because bulk load can only be used on the server?

Thanks alot.

 

[Pattycake245]

You will have to create the table first and assign that last column as a varchar. Because the other values for that column are numbers SQL assumes that column to be of type int, so the "8C" value will not import in.

Tim

create table newtable(C1 int, C2 char(1), C3 varchar(2))

 

[zhonghandle]

Thanks.

I created the table and assigned the column type to be varchar for column C3. Then, I used Microsoft.Jet.OLEDB.4.0 to query the csv file and assign the results as a recordset. And the string value '8C' still doesn't get loaded. I used the VBScript code in a DTS package. Since the csv file name changes dynamically, i can't use import data option.

 

[Pattycake245]

I juts tried it with the import wizard and it transfered in fine, unless you are trying to do this with a bulk insert or something.

Tim

 

[zhonghandle]

Yes, I tried the import wizard and it works just fine. However, since the csv file name changes day to day. I have to create a script that automatically load the .csv files. So this is what i did:

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FilePath & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""

'// Open CSV file recordset
objRecordSet.Open "SELECT * FROM "& strFileName, _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

And then insert the recordset into the table I created in the database. In this case, all the string values like '8C' are missing (null value).

Thanks.