Importing csv file

[Guest]

I plan to have several csv files to import to access on a weekly basis. I am
having trouble figuring out how to do this efficiently. A sample file format
is below. The text before the colon is the table name and there is more than
1 trainee per file. The csv file format is pre set. Any help would be
appreciated.

Vendor:TESTVENDOR
Company ID:G022
Date:12/17/99
TRAiNEE:250992222;SHIRLEY M. SMITH;F;01;II;150;40;480
COMMENTS:FIRST WEEK;
ADDRESS:88 BAKER ST.;SECOND FLOOR;SUITE
200;COLUMBIA;SC;29170;USA;803-123-4567;;803-222-1111
DEDUCTION:FEDL;100
DEDUCTION:STAT;50
DAILY:12/13/99;8.00;0.00;12;0.00;0.00
DAILY:12/14/99;8.00;0.00;12;0.00;0.00
DAILY:12/15/99;8.00;0.00;12;0.00;0.00
DAILY:12/16/99;8.00;0.00;12;0.00;0.00
DAILY:12/17/99;8.00;0.00;12;0.00;0.00

 

[John Nurick]

What you have is not a CSV file. It's a text file, but of a kind that
Access can readily import. Access's built-in text import facilities
(TransferText) are designed for text files in which every line has
exactly the same complement of fields.

To complicate things further, the data in the file cannot be imported
into a single normalised table. It needs at least two tables (Trainees
and DailyDetails) and maybe more: e.g. Vendors, Trainees, Deductions,
and whatever entitie(s) the "DAILY" records represent.

There are basically two ways to go, each of which means writing code.
Which is best is a matter of taste:

1. Read the file line by line.
- Parse each line into its component field(s).
- Continue until you've got all the values for one record in one of
your tables.
- Append the record to the appropriate table, either with
recordset operations or by building and executing a single-
record append query.
- Continue until you get to whatever marks the end of a record in
the text file (maybe a blank line, maybe just the next line that
begins with "Vendor:").
- Repeat until you get to the end of the file.

2. Read the file line by line, parsing the lines and creating two or
more regular CSV or tab-separated files, one for each table, which you
can then import using the standard DoCmd.TransferText .

 

[Guest]

Thank you for you response. I should have stated text file. I had previously
imported the file using (TransferText) into 1 table. And reading that line by
line using code place the line with 1 field into the correct table. The lines
with multilpe fields are giving me trouble. If you (or someone) could give me
some sample code to get started with it would help.

 

[Ronald Roberts]

Try something like this. This was really quick and dirty,
but it may help you work out your logic. The logic in the
Address and Daily case statements are the same. The only thing
different is where the resulta are stored.

The test file was imported as a 1 field text file of 255 characters.

Ron


Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
Dim db As Database
Dim rs As Recordset
Dim L As Integer
Dim X As Integer
Dim Y As Integer
Dim Z As Integer
Dim iFldCnt As Integer
Dim txtString As String
Dim Addr1 As String
Dim Addr2 As String
Dim Addr3 As String
Dim City As String
Dim State As String
Dim ZipCode As String
Dim Country As String
Dim Phone1 As String
Dim Phone2 As String
Dim Phone3 As String
Dim Date1 As String
Dim Num1 As String
Dim Num2 As String
Dim Num3 As String
Dim Num4 As String
Dim Num5 As String

Dim txtCmd As String
Dim txtChar As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Test_Data_Import")
Do Until rs.EOF
X = 0
txtString = " "
txtCmd = " "
X = InStr(1, rs!Field1, ":")
If X > 0 Then
txtCmd = left(rs!Field1, X)
Else
'error routine
End If
Z = X
Select Case txtCmd
Case "Vendor:"
Case "Company ID:"
Case "Date:"
Case "TRAINEE:"
Case "COMMENTS:"
Case "ADDRESS:"
iFldCnt = 0
Y = X + 1
For L = X + 1 To Len(rs!Field1)
txtChar = Mid(rs!Field1, Y, 1)
If txtChar = ";" Then
iFldCnt = iFldCnt + 1
txtString = Mid(rs!Field1, Z + 1, Y - Z - 1)
Z = Y
Select Case iFldCnt
Case 1
Addr1 = txtString
Case 2
Addr2 = txtString
Case 3
Addr3 = txtString
Case 4
City = txtString
Case 5
State = txtString
Case 6
ZipCode = txtString
Case 7
Country = txtString
Case 8
Phone1 = txtString
Case 9
Phone2 = txtString
End Select
End If
Y = Y + 1
Next L
Phone3 = Mid(rs!Field1, Z + 1, Y - Z)

Case "DEDUCTION:"
Case "DAILY:"
iFldCnt = 0
Y = X + 1
For L = X + 1 To Len(rs!Field1)
txtChar = Mid(rs!Field1, Y, 1)
If txtChar = ";" Then
iFldCnt = iFldCnt + 1
txtString = Mid(rs!Field1, Z + 1, Y - Z - 1)
Z = Y
Select Case iFldCnt
Case 1
Date1 = txtString
Case 2
Num1 = txtString
Case 3
Num2 = txtString
Case 4
Num3 = txtString
Case 5
Num4 = txtString
Case 6
Num5 = txtString
End Select
End If
Y = Y + 1
Next L
Num5 = Mid(rs!Field1, Z + 1, Y - Z)
Case Else
'error routine
End Select
rs.MoveNext
Loop

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

 

[Guest]

Thank you so much that was a huge help.

Try something like this. This was really quick and dirty,
but it may help you work out your logic. The logic in the
Address and Daily case statements are the same. The only thing
different is where the resulta are stored.

The test file was imported as a 1 field text file of 255 characters.

Ron


Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
Dim db As Database
Dim rs As Recordset
Dim L As Integer
Dim X As Integer
Dim Y As Integer
Dim Z As Integer
Dim iFldCnt As Integer
Dim txtString As String
Dim Addr1 As String
Dim Addr2 As String
Dim Addr3 As String
Dim City As String
Dim State As String
Dim ZipCode As String
Dim Country As String
Dim Phone1 As String
Dim Phone2 As String
Dim Phone3 As String
Dim Date1 As String
Dim Num1 As String
Dim Num2 As String
Dim Num3 As String
Dim Num4 As String
Dim Num5 As String

Dim txtCmd As String
Dim txtChar As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Test_Data_Import")
Do Until rs.EOF
X = 0
txtString = " "
txtCmd = " "
X = InStr(1, rs!Field1, ":")
If X > 0 Then
txtCmd = left(rs!Field1, X)
Else
'error routine
End If
Z = X
Select Case txtCmd
Case "Vendor:"
Case "Company ID:"
Case "Date:"
Case "TRAINEE:"
Case "COMMENTS:"
Case "ADDRESS:"
iFldCnt = 0
Y = X + 1
For L = X + 1 To Len(rs!Field1)
txtChar = Mid(rs!Field1, Y, 1)
If txtChar = ";" Then
iFldCnt = iFldCnt + 1
txtString = Mid(rs!Field1, Z + 1, Y - Z - 1)
Z = Y
Select Case iFldCnt
Case 1
Addr1 = txtString
Case 2
Addr2 = txtString
Case 3
Addr3 = txtString
Case 4
City = txtString
Case 5
State = txtString
Case 6
ZipCode = txtString
Case 7
Country = txtString
Case 8
Phone1 = txtString
Case 9
Phone2 = txtString
End Select
End If
Y = Y + 1
Next L
Phone3 = Mid(rs!Field1, Z + 1, Y - Z)

Case "DEDUCTION:"
Case "DAILY:"
iFldCnt = 0
Y = X + 1
For L = X + 1 To Len(rs!Field1)
txtChar = Mid(rs!Field1, Y, 1)
If txtChar = ";" Then
iFldCnt = iFldCnt + 1
txtString = Mid(rs!Field1, Z + 1, Y - Z - 1)
Z = Y
Select Case iFldCnt
Case 1
Date1 = txtString
Case 2
Num1 = txtString
Case 3
Num2 = txtString
Case 4
Num3 = txtString
Case 5
Num4 = txtString
Case 6
Num5 = txtString
End Select
End If
Y = Y + 1
Next L
Num5 = Mid(rs!Field1, Z + 1, Y - Z)
Case Else
'error routine
End Select
rs.MoveNext
Loop

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub