.csv import into sql2000

Stumped on getting data from .csv file into sql table
good connection, table creation via sql is solid, now need to populate table with data.

1st question is how to reference the .csv file - current code is

cnWBS.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=WBS;Data Source=CPU-0324\NEWWBS"
cnWBS.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=\c:\wbsqs_v5\import\crs\new\plu_010802.csv\;" & _
"Extensions=asc,csv,tab,txt;"

Set Cmd.ActiveConnection = cnWBS
Cmd.CommandText = "INSERT INTO [WBS].[dbo].[s_form] ([PLU], [Desc1], Price) SELECT Plu_010802.[PLU#], Plu_010802.Desc, Plu_010802.TOTAL From "c:\wbsqs_V5\Import\crs\new\Plu_010802.csv; "
Cmd.CommandType = adCmdText
Cmd.Execute

gives error invalid string attribute.

2nd question - Then do I need to open the file and read each line into the sql string? Seems that I should be able to do it without specifically opening and looping through the file.

Thanks in advance for your comments and suggestions

perhaps your invalid string comes from



Notice you have 3 quotations marks.... it doesn't add up

for reading, most people do as input, i like to use FSO, it's pretty simple to use and for what you're doing you don't need much more. TS.readall, do splits on the crlf first, then do a split of each element of that array on commas, than just do inserts.

Thanks for the input,

Part 1 problem was mostly due to calling out the wrong table.
(must have been tired, ........)

Part 2 re the inserts. what is the best way to pass a value to the insert statement. The references I have only show quoted values ie VALUES ('this','that') nothing on using a variable and the error msg I get when defining one and using as VALUES ( var1, var2) say this is not allowed can only use constants, expressions or variables. So how is a variable used here?

thanks
RK

1) I'd recommend running the queries strait up at the command prompt of the db to see if it works first, then put it into the code, saves a lot of time

2) are you talking about inserting variables? it's just a string.... so

sqlstr= "INSERT INTO tbl_name (col1,col2) VALUES(' " & blah & " ',' " & blah2 & "';"

just concatenate the variables in there with the & operator

Ah yes, the proper use of single and double quotes to concatentate. Thanks

making some progress here, so this works except for one item
Var1 = 455
Var2 = "bk Wathabr"
Var3 = 45.6
Cmd.CommandText = "INSERT INTO [WBS].[dbo].[pludec38] ([PLU], [Dscrpt1], Price) VALUES ('" & Var1 & " ','" & Var2 & "','" & Var3 & "');"

in the table the third col is defined as smallmoney, so there is an error - " not able to do an implicit cast. Use convert"
So convert is applied as follows.

Cmd.CommandText = "INSERT INTO [WBS].[dbo].[pludec38] ([PLU], [Dscrpt1], Price) VALUES ('" & Var1 & " ','" & Var2 & "','" & Convert(smallmoney,Var3) & "');"

This produces an error that the variable smallmoney is not defined. Yet it is part of the convert definitions, same error using cast. Any ideas as to why this occuring?
Thanks
RK

As near as I can determine Cast() and Convert() only work on data being brought out from the table, not on data going in. Therefore It seems that I should make the table datatype a string and then convert it for use when I query it.

Comments?

Thanks
RK

The internal format of the SMALLMONEY data type requires a scale of 4 (i.e., 4 digits after the decimal place). This can not be altered. You can however, use the CONVERT function to return to the client application only two decimal places.

Personally, I'd recommend, in your queries, casting the SMALLMONEY data type to something like DECIMAL (8, 2), for example:

declare @Doron smallmoney
set @Doron = 88.8888
select cast (@Doron as decimal(8,2))

However you could also use the CONVERT to change it to a character string with only two decimal places:

declare @Doron smallmoney
set @Doron = 88.8888
select convert (varchar, @Doron, 0)

so, try updating it with 4 decimal places.
If this doesn't help, i recommend taking it over to the database forum. I hoped i was able to clear up any confusion on smallmoney and the usage of convert and cast, but I'm just not the DB master like the smart fellows on the DB form, good luck.

thanks for comments you've been a great help,

I'll move over to the DB forum and continue there.

RK