Parse a csv file

I need ASP/vbscript using FileSystemObject to read and insert fields from a tab-delimited file (overtime.csv) into a MS SQL database. Unfortunately, this particular host does not allow BULK INSERT.

Sample file attached. Field names in SQL match names in csv.

SQL structure
dept      nvarchar      50      1      
last      nvarchar      50      1      
first      nvarchar      50      1      
ssn      int      4      1      
regwage      float      8      1      
otwage      float      8      1      
tlwage      float      8      1      

overtime.csv sample

DEPT      LAST      FIRST      SSN      REGWAGE      OTWAGE      TLWAGE
QQQ007      Jon      Smith      123456789      1153.85      0      1153.85
QQQ007      Joe      Wilson      987654321      4230.77      0      4230.77
QQQ007      Jane      Doe      012345678      540      0      540
griffawAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Just some notes here on data types:

1) By using int for SSN, you are aware that leading zeroes will be dropped, right?

2) I would discourage the use of float for the wage columns.  Use a numeric type instead, such as numeric(10, 2).  That would allow for up to 10 significant figures, of which two are reserved for decimals.  Float should be avoided because it is imprecise
0
griffawAuthor Commented:
matthewspatrick:

That works for me. I'll change the SSN to VARCHAR, and the flots to numeric 10,2. Any luck on the script?
0
griffawAuthor Commented:
New SQL structure:

dept      nvarchar                  
last      nvarchar                  
first      nvarchar                  
ssn      nvarchar                  
regwage      numeric (10,2)            
otwage      numeric(10,2)            
tlwage      numeric(10,2)            
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Patrick MatthewsCommented:
Try something like this...


Dim fso, ts, conn, arr

Const dbserver = "DbServe"
Const dbname = "DbName"
Const dbuserid = "username"
Const dbpasswd = "password"

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile("c:\overtime.csv")
Set conn = CreateObject("ADODB.Connection") 

conn.Open "Provider=sqloledb;" & _
    "Data Source=" & dbserver & ";" & _
    "Initial Catalog=" & dbname & ";" & _
    "User ID=" & dbuserid & ";" & _
    "Password=" & dbpasswd & ";"

Do Until ts.AtEndOfStream
    arr = Split(ts.ReadLine, ",")
    conn.Execute "INSERT INTO SomeTable (DEPT, LAST, FIRST, SSN, REGWAGE, OTWAGE, TLWAGE) " & _
        "VALUES ('" & arr(0) & "', '" & arr(1) & "', '" & arr(2) & "', '" & arr(3) & "', " & _
        arr(4) & ", " & arr(5) & ", " & arr(6) & ")"
Loop

ts.Close
Set ts = Nothing
Set fso = Nothing

conn.Close
Set conn = Nothing

MsgBox "Done"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
griffawAuthor Commented:
matthewspatrick:

i get the folllowing error on conn.Execute line


Microsoft VBScript runtime error '800a0009'

Subscript out of range: '[number: 1]'

0
griffawAuthor Commented:
matthewspatrick:
 I just noticed your script split the line on ",". This is a tab delimited file. changed the split to
    arr = Split(ts.ReadLine, vbtab)
and got the following error

Microsoft OLE DB Provider for SQL Server error '80040e14'

Unclosed quotation mark before the character string 'ÿþD'.
0
Patrick MatthewsCommented:
Please upload a sanitized (i.e., personal information obfuscated) copy of the actual file.

Sorry for not noticing that this was tab delimited.  CSV files are typically comma-delimited :)
0
griffawAuthor Commented:
matthewspatrick:

File attached
OvertimeReporttest.csv
0
hieloCommented:
...
Do Until ts.AtEndOfStream

    'Here you need to escape the apostrophes
    arr = Split( Replace(ts.ReadLine,"'","''"), vbTab)

    conn.Execute "INSERT INTO SomeTable (DEPT, LAST, FIRST, SSN, REGWAGE, OTWAGE, TLWAGE) " & _
        "VALUES ('" & arr(0) & "', '" & arr(1) & "', '" & arr(2) & "', '" & arr(3) & "', " & _
        arr(4) & ", " & arr(5) & ", " & arr(6) & ")"
Loop
...
0
griffawAuthor Commented:
hielo:

Still the same error:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Unclosed quotation mark before the character string 'ÿþD'.
0
griffawAuthor Commented:
Note:

I'm not sure if it matters...but when I open the file in Excel and resave, I get a warning that the file is Unicode.
0
hieloCommented:
It sounds like an encoding issue. Try pasting copy of your data to notepad and under the Encoding option use "ANSI".
Then try that as your csv file.
0
griffawAuthor Commented:
hielo:

I pasted to notepad, saved as .csv ANSI and tried the script. Got the following error:

Microsoft OLE DB Provider for SQL Server error '80040e14'

The name 'REGWAGE' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
0
hieloCommented:
Try enclosing the field/column names in []:
...
Do Until ts.AtEndOfStream

    'Here you need to escape the apostrophes
    arr = Split( Replace(ts.ReadLine,"'","''"), vbTab)

    conn.Execute "INSERT INTO SomeTable ([DEPT], [LAST], pFIRST[, [SSN], [REGWAGE], [OTWAGE], [TLWAGE]) " & _
        "VALUES ('" & arr(0) & "', '" & arr(1) & "', '" & arr(2) & "', '" & arr(3) & "', " & _
        arr(4) & ", " & arr(5) & ", " & arr(6) & ")"
Loop
...
0
griffawAuthor Commented:
hielo:

I added [ ] and Got the following error.

Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near ', [SSN'.

0
griffawAuthor Commented:
hielo:

I corrected the bracket issue in your select. See below.

conn.Execute "INSERT INTO SomeTable ([DEPT], [LAST], [FIRST], [SSN], [REGWAGE], [OTWAGE], [TLWAGE]) " & _
        "VALUES ('" & arr(0) & "', '" & arr(1) & "', '" & arr(2) & "', '" & arr(3) & "', " & _
        arr(4) & ", " & arr(5) & ", " & arr(6) & ")"

I get the following error.

Microsoft OLE DB Provider for SQL Server error '80040e14'

The name 'REGWAGE' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
0
hieloCommented:
On my last post I have:
...,[LAST], pFIRST[, [SSN],...

clearly pFirst has a typo. It should be:
...,[LAST], [pFIRST], [SSN],...
0
hieloCommented:
Does it insert at least one record?

Try:
...
Do Until ts.AtEndOfStream

    'Here you need to escape the apostrophes
    arr = Split( Replace(ts.ReadLine,"'","''"), vbTab)
   If UBound(arr)=6 Then
    conn.Execute "INSERT INTO SomeTable ([DEPT], [LAST], [FIRST], [SSN], [REGWAGE], [OTWAGE], [TLWAGE]) " & _
        "VALUES ('" & arr(0) & "', '" & arr(1) & "', '" & arr(2) & "', '" & arr(3) & "', " & _
        arr(4) & ", " & arr(5) & ", " & arr(6) & ")"
   End If
Loop
...
0
griffawAuthor Commented:
No records are entered.

Still the same error
Microsoft OLE DB Provider for SQL Server error '80040e14'

The name 'REGWAGE' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
0
hieloCommented:
are running that code in an ASP page or somewhere else?
0
jostranderCommented:
Hi all, just peeking here, it looks like you're using a csv file that has headers in it... I think you're getting hung up on line 1.

The sql code INSERT is trying to insert some of these headers as numeric, without apostrophes.
0
hieloCommented:
>>Hi all, just peeking here, it looks like you're using a csv file that has headers in it.
It would have helped if I had opened the sample file!

Right before:
Do Until ts.AtEndOfStream

put:
ts.ReadLine

so that the headers line get skipped
0
griffawAuthor Commented:
jostrander: , hielo:

That did it...but it wont work on my original csv file....how do I convert from Unicode to Ansi on the fly?

Here's the error now ont he conn.Execute line

Microsoft OLE DB Provider for SQL Server error '80040e14'

Unclosed quotation mark before the character string ''.
0
jostranderCommented:
You can open the original file as UNICODE by changing your fso.OpenTextFile line:

Set ts = fso.OpenTextFile("c:\overtime.csv")

to this:

Set ts = fso.OpenTextFile("c:\overtime.csv",1,,true)
0
hieloCommented:
http://www.w3schools.com/asp/met_opentextfile.asp

try changing:
Set ts = fso.OpenTextFile("c:\overtime.csv")

to:
Set ts = fso.OpenTextFile("c:\overtime.csv",1,,-1)
0
griffawAuthor Commented:

SUCCESS!!!!!
Thank you all so much. I had looked at it so long my head hurt. I'm not sure how to spread the accepted solution love...but I'll figure it out. The complete script is below.

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile("C:\Overtime.csv",1,,true)
Set conn = CreateObject("ADODB.Connection")  
 
conn.Open connection

 ts.ReadLine

Do Until ts.AtEndOfStream

    'Here you need to escape the apostrophes
    arr = Split( Replace(ts.ReadLine,"'","''"), vbTab)
   If UBound(arr)=6 Then
    conn.Execute "INSERT INTO sometable ([DEPT], [LAST], [FIRST], [SSN], [REGWAGE], [OTWAGE], [TLWAGE]) " & _
        "VALUES ('" & arr(0) & "', '" & arr(1) & "', '" & arr(2) & "', '" & arr(3) & "', " & _
        arr(4) & ", " & arr(5) & ", " & arr(6) & ")"
   End If
Loop

 
ts.Close
Set ts = Nothing
Set fso = Nothing
 
conn.Close
Set conn = Nothing
 
MsgBox "Done"
0
jostranderCommented:
Either way actually works... it's funny, their example actually uses "True" as well.

As well as many other sites:
http://www.devguru.com/technologies/vbscript/quickref/filesystemobject_opentextfile.html
http://www.websupergoo.com/helppdf5/source/4-examples/12-unicode.htm

0
griffawAuthor Commented:
Outstanding support. Great speed. Perfect solution. Many thanks to all.
0
jostranderCommented:
Scratch that... I'm going bugeyed looking at code...

Good work griffaw!
0
jostranderCommented:
Good work Hielo!
0
jostranderCommented:
Sorry forgot one...Good work matthewspatrick!

I'll shut up now... :)
0
Patrick MatthewsCommented:
Wow, I go away for a while and I see that a whole cadre of terrific Experts stepped into the breach!

Glad to have helped, and even gladder that those other Experts jumped in to lend a hand.

Cheers,

Patrick
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.