csv / txt import to one row, not columns.

[pmalfroid]

Hi All,

Im trying to import a csv file into a database as one row. The CSV file contains the dimensions for a autocad drawing, which will need to be verified in a database so it can proven correct. Each row will be for a different print number. The CSV will have a minimum of 8 and a max of 80 dimensions. Below is a sample.

A,1.1,2,1.5
B,2.02,2,999
C,5.002,2,22

The database is loading each line as a separate record, and i need all the data to be in one record / row. I need it to import as one long string. Below is my code that loads each line as a separate record. What do i need to change to make it read the file as one string? (and the string might be 8 to 80, the way im doing it calls an error if it isnt exact.)

Option Compare Database
Dim db_myDatabase As Database

Dim rs_recordset As Recordset
Private Sub Form_Load()

Set db_myDatabase = OpenDatabase("c:\temp\mydatabase.accdb")
Set rs_recordset = db_myDatabase.OpenRecordset("myTable")
End Sub
Private Sub Command22_Click()
Dim arrData() As String
Dim s As String
Dim i As Integer
Open "C:\temp\Card.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, s
arrData = Split(s, ",")
For i = 0 To UBound(arrData)
Debug.Print arrData(i)
Next i

With rs_recordset
.AddNew
!date1 = arrData(0)
!amount1 = arrData(1)
!description1 = arrData(2)
!test1 = arrData(3)

'if i edit all the text in card.txt to one line then
'the below code works

'!date2 = arrData(4)
'!amount2 = arrData(5)
'!description2 = arrData(6)
'!test2 = arrData(7)


'!date3 = arrData(8)
'!amount3 = arrData(9)
'!description3 = arrData(10)
'!test3 = arrData(11)
.Update
End With
Loop
Close #1
End Sub

Private Sub Form_Unload(Cancel As Integer)
rs_recordset.Close
db_myDatabase.Close
Set rs_recordset = Nothing
Set db_myDatabase = Nothing
End Sub

Any help would be appreciated. Thank you in advance!

[June7]

Do I understand correctly - you want the entire CSV file as a single long string in one field?

Don't parse the line to an array with Split function. This is breaking each line into separate values. Use loop to concatenate each line of the CSV into a single string variable, something like:

Dim F as Long
Dim s As String
Dim sAll As String
F = FreeFile
Open "C:\temp\Card.txt" For Input As #F
Do While Not EOF(F)
Line Input #F, s
sAll = sAll & s & " "
Loop

[ssanfu]

If I understand right,
- you have a text file in CSV format
- there will be a minimum of 8 rows and a maximum of 80 rows
- you want to convert the rows to columns

- the fields are : date1, amount1, description1, test1, date2, amount2, description2, test2, date3, amount3, description3, test3, ....
If there are 80 lines (dimensions??) per CSV file, then there would be 320 fields (minimum). Office 2010 has a maximum of 255 fields per table. Bit of a problem.....


How you wrote your code is a little unusual , but it seems to work
I changed your code to write the rows to columns, but it looks like you will run out of fields in the table...

Option Compare Database
Option Explicit   'should always have this line

Dim db_myDatabase As DAO.Database
Dim rs_recordset As DAO.Recordset

Private Sub Form_Load()
   Set db_myDatabase = OpenDatabase("c:\temp\mydatabase.accdb")
   Set rs_recordset = db_myDatabase.OpenRecordset("myTable")
End Sub

Private Sub Form_Unload(Cancel As Integer)
   rs_recordset.Close
   db_myDatabase.Close
   Set rs_recordset = Nothing
   Set db_myDatabase = Nothing
End Sub

Private Sub Command22_Click()
   Dim arrData
   Dim s As String
   Dim i As Integer
   Dim FF As Integer  'next file number available for use by the Open statement

   i = 1                ' initalize counter
   FF = FreeFile   'Use FreeFile to supply a file number that is not already in use.
   Open "C:\temp\Card.txt" For Input As #FF

   'create a new record and move to it
   rs_recordset.AddNew
   rs_recordset.Update
   rs_recordset.Bookmark = rs_recordset.LastModified

   Do While Not EOF(FF)
      Line Input #FF, s
      arrData = Split(s, ",")

      rs_recordset.Edit
      rs_recordset("date" & i) = arrData(0)
      rs_recordset("amount" & i) = arrData(1)
      rs_recordset("description" & i) = arrData(2)
      rs_recordset("test" & i) = arrData(3)
      rs_recordset.Update

      i = i + 1   'increment counter
'      If i > 80 Then
'         'do something
'      End If

      'clear the array - just in case
      arrData(0) = Empty
      arrData(1) = Empty
      arrData(2) = Empty
      arrData(3) = Empty

   Loop

   Close #FF   'close text file

   MsgBox "Done"
End Sub

Let me know if I missed something....

Edit: June7 beat me again. I've got to learn how to type faster...

[pmalfroid]

Thank you June7, but that would put the whole string into one column? er no?

Hi Steve

Your comment from above..

If I understand right,
- you have a text file in CSV format
- there will be a minimum of 8 rows and a maximum of 80 rows < nope, 2 rows in the csv max of 20. with 4 dimensions per row. A total of 80 columns (dimensions).
- you want to convert the rows to columns < yes

- the fields are : date1, amount1, description1, test1, date2, amount2, description2, test2, date3, amount3, description3, test3, ....up to date20,amount20....

If there are 80 lines (dimensions??) per CSV file "total" 80 fields then there would be 320 fields (minimum). Office 2010 has a maximum of 255 fields per table. Bit of a problem.....

How you wrote your code is a little unusual , but it seems to work <yes, i googled it about a month ago and have been racking my head against the wall since. I know access pretty well but not VB and importing so ive been reading up alot on it. Will the code you wrote work with the 80 columns?

[June7]

Yes, that is what the code would do. I don't really understand what you what. What does "What do i need to change to make it read the file as one string?" mean to you?

Your data sample doesn't coincide with the field names you show in code. I don't see any date or description values.

If ssanfu's suggestion still doesn't accomplish what you want, show an accurate sample of the source data and what you want the output to look like.

Great insight ssanfu, looks like you got the point I missed. First doesn't equate to best!

[pmalfroid]

Thank you Steve...

You nailed it on the head! Its exactly what i needed! Thank you and i will put the credits in the code for you. Thank you!!

Now i can pick it apart and learn more!!!

Sorry June7, I had a hard time trying to explain it. Thank you for your time, we noob's appreciate it!

[ssanfu]

You're welcome.

Will the code you wrote work with the 80 columns?

It should. Just as long as the number of rows times dimensions isn't greater than the number of fields in the table.
Also note that there is no error handler...

If you want, I could show you how I would write the code (my way, not the only way). Nothing is drastically wrong, just strange where you have put some code.
Good luck with your project.

[pmalfroid]

It should. Just as long as the number of rows times dimensions isn't greater than the number of fields in the table. <you are correct.
Also note that there is no error handler... <you are also correct. Need to use Task Manager to break out at times...


If you want, I could show you how I would write the code (my way, not the only way). Nothing is drastically wrong, just strange where you have put some code.
Good luck with your project. < I would greatly appreciate it. I would like to send you the Access File, Im almost finished, but dont want to post it onlne. Please Email me at
with "access" in the subject line when you have time. It would be nice to know the "correct way" and not the "googled way"

Thankyou again,
Paul