August 10, 2010 at 8:34 am
Hi,
I'm trying to parse a csv file I've uploaded to a web server using vbscript. Once uploaded I have to insert into a sql server database.
The problem being in the csv file there seems to be an anomilly which i think is a carriage return (in excel it show as a small square). I've tried to remove any characters such as a new line etc, but i'm having to search for the individual string / record where this new line occurs and then do some logic on it. It's a pretty slow solution and not very robust.
Would anyone be able to point me in the correct direction on how to improve this matter.
The code is below>..
Thanks
'############################################
if FSO.FileExists(Filepath) Then
' Get a handle to the file
Dim f
set f = FSO.GetFile(Filepath)
'Open the file
Dim ts
'Set TextStream = FSO.OpenTextFile(f, ForReading, TristateUseDefault)
Set ts = f.OpenAsTextStream()
dim output
'Read the file line by line
Do While Not ts.AtEndOfStream
output = ts.ReadLine
if InStr(1,output,"Harry Higgins") OR InStr(1,output,"22 industrial estate") then
if InStr(1,output,"1 East Lane") then
stSql = stSql2 & replace(output, chr(34), chr(39) )&")"
stSql = replace(stSql, ",)", ","""")")
stSql = replace(stSql, ",,", ","""",")
stSql = replace(stSql, ", )", ","""")")
'replace double quotes with single quotes
stSql = trim(replace(stSql, chr(34), chr(39) ))
'response.Write(stSql & "
")
else
stSql2 = "INSERT INTO parralel56(KEY5, COMPANY, CONTACT, ADDRESS1, ADDRESS2 , TOWN, POSTCODE, UCODESC, UEMPBAND, UJOINED, PHONE1, FAX, INETADDR, WEBSITE, USCCND1, USCCND2, USCCND3) VALUES("& output
end if
else
stSql = "INSERT INTO parralel56(KEY5, COMPANY, CONTACT, ADDRESS1, ADDRESS2 , TOWN, POSTCODE, UCODESC, UEMPBAND, UJOINED, PHONE1, FAX, INETADDR, WEBSITE, USCCND1, USCCND2, USCCND3) VALUES("& replace(replace(output, ",,",","""","), ",)", ","""")") & ")"
stSql = replace(stSql, ",)", ","""")")
stSql = replace(stSql, ",,", ","""",")
stSql = replace(stSql, ", )", ","""")")
'replace single quotes with empty space
stSql = trim(replace(stSql, chr(39), "" ))
'replace double quotes with single quotes
stSql = trim(replace(stSql, chr(34), chr(39) ))
'response.Write(stSql & "
")
end if
objConn.Execute stSql, , adExecuteNoRecords + adCmdText
Loop
Set ts = nothing
August 10, 2010 at 10:51 am
I think you would have a lot better luck if you opened the csv as a datasource instead of a filestream. Then you can read the rows directly into an adodb recordset which will let you do whatever you need to do to insert it into sql with a lot less pain. 🙂
something like this.
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath(".\csv\") & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"""
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection
Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM myFile.csv"
rs.open strSQL, conn, 3,3
rs.MoveFirst
WHILE NOT rs.EOF
'do something here
strSQL = "Insert into myTable (Mycolumn) values ( '" & rs("MyColumn") & "')"
rs.MoveNext
WEND
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2010 at 4:07 am
Thanks, that makes sense, I'll give that a try. The only problem being the csv file doesn't have column names so I don't know what the column name would be for each column when looping through the recordset.
Thanks for all your help
August 12, 2010 at 7:00 am
You can reference them by ordinal position also. rs(0) for the first column etc.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2010 at 9:20 am
Thanks for your help
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply