Easty
asked on
importing a csv in to a table. Recognising line breaks
I want to insert values from a csv file in to a table.
Im having trouble in recognising an EOL character
When i try and insert buffer(2) which is the last field i insert, it also has the date from the next line so the program isnt recognising when the 1st line is finished and the 2nd line has started
From the code i am also going through the code charachter by charater is there an easy way to go through line by line.
Open MyPathFile For Input As #1
Do While Not EOF(1)
Do While MyChar <> vbLf
MyChar = Input(1, #1)
DataIn = DataIn + MyChar
Loop
buffer = Split(DataIn, ",")
DoCmd.RunSQL "INSERT INTO tlbTrans (DateOfTrans, PayeeName, Amount) " + _
"VALUES ('" & buffer(0) & "','" & buffer(1) & "'," & buffer(2) & ")"
Im having trouble in recognising an EOL character
When i try and insert buffer(2) which is the last field i insert, it also has the date from the next line so the program isnt recognising when the 1st line is finished and the 2nd line has started
From the code i am also going through the code charachter by charater is there an easy way to go through line by line.
Open MyPathFile For Input As #1
Do While Not EOF(1)
Do While MyChar <> vbLf
MyChar = Input(1, #1)
DataIn = DataIn + MyChar
Loop
buffer = Split(DataIn, ",")
DoCmd.RunSQL "INSERT INTO tlbTrans (DateOfTrans, PayeeName, Amount) " + _
"VALUES ('" & buffer(0) & "','" & buffer(1) & "'," & buffer(2) & ")"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have to do a number of calculations on the data before it goes in to the table.
Does this affect a make-table query? I have never used 1 before.
Does this affect a make-table query? I have never used 1 before.
Are you can you not perform those calculations on a linked table?
Go go the file menu and use the Get external data option. Select Link Tables. Go out and find your CSV file. Link it.
It will now be a resident table in your DB that you can perform any kind of calculations you want through code or query an write to a Permanent table.
It will now be a resident table in your DB that you can perform any kind of calculations you want through code or query an write to a Permanent table.
ASKER
I want to do the calculations on the data before i insert it in to the table.
otherwise i will be doing the calculations at run time.
Thanks
otherwise i will be doing the calculations at run time.
Thanks
J