Importing csv data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there

I want to import a csv file into a table. The format of the csv file is
fixed and I can not change it.

The formart looks like

ABC,FGD,123,TEST
,,124,TEST
,,125,TEST2
FGD,HJY,123,DATA
HJY,YUI,458,DATA2
,,852,DATA
etc

In this format the data is not meaningful in a Table

The format I require is

ABC,FGD,123,TEST
ABD,FGD,124,TEST
ABD,FGD,125,TEST2
FGD,HJY,123,DATA
HJY,YUI,458,DATA2
HJY,YUI,852,DATA
etc

How can I amend the data to be more meaningful?

Thanks in advance for any help
Graham
 
Graham,

The only way I can think of to handle this is through VBA code which
imports the .csv file line by line and appends records to the table
through a recordset operation. Here's some sample aircode:

Sub import_CSV()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim vF(1)
vFile = "C:\SomeFolder\MySourceFile.csv"
vTable = "MyDestinationTable"
If Dir(vFile) = "" Then
MsgBox "File does not exist.", vbCritical
Exit Sub
End If
Open vFile For Input As #1
Set db = CurrentDb
Set rst = db.OpenRecordset(vTable)
Do Until EOF(1)
Line Input #1, vLine
vFields = Split(vLine, ",")
For i = 0 To 1
If vFields(i) <> "" Then
vF(i) = vFields(i)
Else
vFields(i) = vF(i)
End If
Next
rst.AddNew
For i = 0 To 3
rst.Fields(i) = vFields(i)
Next
rst.Update
Loop
Close #1
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Note: To run this code, an appropriate DAO Object Library reference is
required. While in the VB editor window, go to menu item Tools >
References; check if a Microsoft DAO reference is present among the ones
checked at the top of the list. If not, scroll down to find the
appropriate Microsoft DAO X.X Object Library reference and check it. The
appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K or later.

As a matter of fact, if you are still on A97 then function Split is not
available, in which case you'll have to use a user-defined function to
do the job; let me know.

HTH,
Nikos
 
Actually, there is another way. You can create in import spec for the csv
file. In that specification, you can identify the field types, names, etc.
Then when you import the file, it will import in the format you need. Here
is the procedure:

File->Get External Data->Import
Select the file you want
Click on the Advanced command button
Select the options and formatting you need. You can skip fields you don't
want and add fields if you need to
When you are done with that, click on Save As and give it a name.

Then you can use either a macro or code to do the import.

Here is the import code:

DoCmd.TransferText acImportDelim, "Pipeline", "Pipeline80", "C:\Documents
and Settings\hargida\My Documents\Access\CISCMS\pipeline80.csv", True, ""
 
Hi Nikos

Thanks for the code I have one slight problem
In the CSV file there are 11 fields of which the first 5 need to be filled
in. I tried to amend teh code but get a "Subcript out of range" error.

Thanks again for any help

Graham
 
I don't see how that's relevant to the question at hand, Klatuu.

As I understand it, Graham doesn't want to skip fields. He wants missing
fields to "inherit" the value of their predecessor.

In other words, he wants the 2nd and 3rd row to use ABC as the first field,
and FGD as the second field.

ABC,FGD,123,TEST
,,124,TEST
,,125,TEST2

I think Nikos's solution is the only way.
 
What did you amend the code to, Graham?

Post it here, and we should be able to help you.
 
Graham,

Sorry for the delay, I was off yesterday. Try this:

Sub import_CSV()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim vF(4)
vFile = "C:\SomeFolder\MySourceFile.csv"
vTable = "MyDestinationTable"
If Dir(vFile) = "" Then
MsgBox "File does not exist.", vbCritical
Exit Sub
End If
Open vFile For Input As #1
Set db = CurrentDb
Set rst = db.OpenRecordset(vTable)
Do Until EOF(1)
Line Input #1, vLine
vFields = Split(vLine, ",")
For i = 0 To 4
If vFields(i) <> "" Then
vF(i) = vFields(i)
Else
vFields(i) = vF(i)
End If
Next
rst.AddNew
For i = 0 To 10
rst.Fields(i) = vFields(i)
Next
rst.Update
Loop
Close #1
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

HTH,
Nikos
 
Thanks Nikos

Just what I wanted.

Graham

Nikos Yannacopoulos said:
Graham,

Sorry for the delay, I was off yesterday. Try this:

Sub import_CSV()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim vF(4)
vFile = "C:\SomeFolder\MySourceFile.csv"
vTable = "MyDestinationTable"
If Dir(vFile) = "" Then
MsgBox "File does not exist.", vbCritical
Exit Sub
End If
Open vFile For Input As #1
Set db = CurrentDb
Set rst = db.OpenRecordset(vTable)
Do Until EOF(1)
Line Input #1, vLine
vFields = Split(vLine, ",")
For i = 0 To 4
If vFields(i) <> "" Then
vF(i) = vFields(i)
Else
vFields(i) = vF(i)
End If
Next
rst.AddNew
For i = 0 To 10
rst.Fields(i) = vFields(i)
Next
rst.Update
Loop
Close #1
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

HTH,
Nikos
Click to expand...
 
Back
Top