Impoting from csv

[Magnus1982]

Hello,


I have csv files in folders


Test [main folder]

Data1[subfolder]
Data2[subfolder]
Data3[subfolder]
​

Folders are creating automaticly by external software (qty going up) . In each Data folder is csv files
In this file in field A9 and A8 is data which i wanna import to table in access


It is possible?.

​

 

[CJ_London]

use transfertext to link to the csv file then use an append/update query as required to extract your two columns

 

[The_Doc_Man]

Adding to CJ's suggestion:

I see that you have not posted a lot and surmise that you might be somewhat of a novice with Access. (If I'm wrong and you are comfortable with Access, I apologize.)

I recommend that you import to a table that you erase before each import step so that you can do testing or validation before actually adding that new information to the place where you want it to stay in the long run. You are less likely to need to perform manual intervention to undo mistakes if you pre-clean your data.

Yes, it takes an extra step or two, but if you can automate it more easily, do less handling, and in general prevent mistakes, it is a worthwhile effort.

You will probably need to do some reading. Since you are talking about having to play with looking in folders for files, I will suggest that you do some web searches (and search this forum) to look at

- Scripting File System Object. (That is its name, don't be put off by it.) That is what you would use to scan for files in folders with different names. This is what you would use to GET the next file name from which to do an import.

You will SOMETIMES see that written as FileSystemObject (without spaces). In order to use this object, you will need a code reference added to your project, which you can do by opening any code module (general or a Class module that is part of a form.) In the Menu bar, click Tools then References. Find the Scripting reference and check it. The list is alphabetically sorted so should be easy to find. Then close the References dialog box and save your module.

- TransferText method is a way to import a file in CSV format. Again, a web or forum search will tell you a lot about it.

As to importing data from the CSV file in two parts:

The first import should drop data into a table, but if there are column or row headers in that file, you might have to remove them first before using the INSERT INTO syntax to move data from the staging table to the final table. Therefore, read up on INSERT INTO syntax.

That might be enough to get you started in the right direction.

 

[Pat Hartman]

In addition to Doc and CJ's suggestions (I use both techniques depending on how reliable the data is), I create a log table to log the import file name and assign a batch ID. So when I append from the linked table or temp table to the permanent table, I include the batchID. This makes it possible to back out data if necessary. For one application, the files must be imported in a specific sequence since they are reconciliation files from the bank and if you import May before April, you won't be able to reconcile anything.

 

[The_Doc_Man]

Good point, Pat. Adding source-file ID allows you to assure you do things in the right order. As a further point, since you can get file properties when using the File System Object, you can get file dates that help to decide which one should be used in which order. That of course requires you to scan for all files beforehand to get their dates, but that would also allow you to recognize if one of the files in question has already been imported. There are a LOT of reasons to do this carefully in stages.

 

[Magnus1982]

Yep that true. I am using/learning access from 1 year . My base to solve issue which are to big for me is google .forums where I can find code or similar code and after i am trying tu understand this code and apply to my access database.



In this case I was able to find and store path for all csv files in table (code is checking folder and subfolders)


No I have to find way to transfer data from this files

 

[June7]

Another method is to open and manipulate text file object.

However, if structure of files is same for all, TransferText to import records to existing table (a 'temp' table - table is permanent and records are temporary) should be simple.

If you have looping code to find files, expand on that to include procedure that imports records when file is located. Writing file name to another table is optional.

 

[Magnus1982]

OK last problems :/




I was able to import what i want. Now how to create a loop (i think) to get path

from tblPath column Pth import first row
then get next path from second row and import

and then get next path import ........loop


Below code . On red is a open statement where automaticly should be placed path from table.Somebody can help?





Private Sub Polecenie96_Click()
Dim i As Long
Dim buffer As String
Dim strSQL As String
Open "Here i need to place value from table tblPath and column Pth (can by more then 300 rows" For Input As #1
Line Input #1, buffer
i = 1
While Not EOF(1)

If i = 8 Or i = 9 Then
DoCmd.SetWarnings False
strSQL = "INSERT INTO [csvimp] ([p1]) VALUES ('" & buffer & "')"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If

Line Input #1, buffer
i = i + 1
Wend

Close #1
End Sub

 

[June7]

That's why I suggested you do the import on the iteration through folders. Now you have to open a recordset object of the table records and iterate through recordset to grab each file path\name.

 

[arnelgp]

if the path is in the table:

Private Sub Polecenie96_Click()
Dim i As Long
Dim buffer As String
Dim strSQL As String
With CurrentDB.OpenRecordSet("tblPath", dbOpenSnapShot)
 If Not (.BOF And .EOF) Then .MoveFirst
 While Not .EOF
  Open (!Pth) For Input As #1
  Line Input #1, buffer
  i = 1
  Do While Not EOF(1)
   If i >= 7 Then
    DoCmd.SetWarnings False
    strSQL = "INSERT INTO [csvimp] ([p1]) VALUES ('" & buffer & "')"
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
   End If
   i = i + 1
   If i > 9 Then Exit Do
   Line Input #1, buffer
  Loop
  Close #1
  .MoveNext
 Wend
End With
End Sub

 

[Magnus1982]

Thank you arnelgp.


It is working perfect. I just modify code to get line 8 and 9 from csv.


One issue which still casing problems is that all information are in the same column


Is it possible to import i=8 to p1 and i=9 to p2. I try to use another if with update statement but didin't work

 

[arnelgp]

yes of course:

Private Sub Polecenie96_Click()
Dim i As Long
Dim buffer As String
Dim strSQL As String
With CurrentDB.OpenRecordSet("tblPath", dbOpenSnapShot)
	If Not (.BOF And .EOF) Then .MoveFirst
	While Not .EOF
		Open (!Pth) For Input As #1
		Line Input #1, buffer
		i = 1
		Do While Not EOF(1)
			If i = 8 Or i = 9 Then
				DoCmd.SetWarnings False
				strSQL = "INSERT INTO [csvimp] ([p" & Abs(7 - i) & "]) VALUES ('" & buffer & "')"
				DoCmd.RunSQL strSQL
				DoCmd.SetWarnings True
			End If
			i = i + 1
			If i > 9 Then Exit Do
			Line Input #1, buffer
		Loop
		Close #1
		.MoveNext
	Wend
End With
End Sub

 

[Magnus1982]

almost.


It is creating row each time

I have:


P1 P2

Data1 Empty (files1)

Empty Data2 (files1)
Data 1 Empty (files2)

Empty Data2 (files2)


I need more like


Data1 Data2 in one row

 

[Magnus1982]

sample:

 

[Magnus1982]

sample now

 

[June7]

Do While Not EOF(1)
    If i = 8 Or i = 9 Then
        strSQL = strSQL & "'" & buffer & "',"
    End If
    i = i + 1
    If i > 9 Then Exit Do
    Line Input #1, buffer
Loop
CurrentDb.Execute = "INSERT INTO [csvimp] (P1,P2) VALUES (" & Left(strSQL, Len(strSQL)-1) & ")"]
Close #1
.MoveNext
strSQL = ""

 

[arnelgp]

Private Sub Polecenie96_Click()
Dim i As Long
Dim buffer As String
Dim strSQL As String
Dim thisBuffer As String
With CurrentDB.OpenRecordSet("tblPath", dbOpenSnapShot)
	If Not (.BOF And .EOF) Then .MoveFirst
	While Not .EOF
		Open (!Pth) For Input As #1
		Line Input #1, buffer
		i = 1
		thisBuffer = vbNullString
		Do While Not EOF(1)
			If i = 9 Then
				DoCmd.SetWarnings False
				strSQL = "INSERT INTO [csvimp] ([p1],[p2]) VALUES ('" & Split(thisBuffer,"|")(0) & "','" &  Split(thisBuffer,"|")(1) & "')"
				DoCmd.RunSQL strSQL
				DoCmd.SetWarnings True
				
				Exit Do
			End If
			i = i + 1
			Line Input #1, buffer
			If i = 8 Or i = 9 Then
				thisBuffer = thisBuffer & buffer & "|"
			End If
			buffer = vbNullString
		Loop
		Close #1
		.MoveNext
	Wend
End With
End Sub

 

[Pat Hartman]

Clearly I've missed something. Why are you using code rather than DoCmd.TransferText

One line of code which allows you to specify a format for the import file so you don't need code to format the record.

To create an import spec, import the file ONCE manually and click the Advanced button before the final step. Name and save the import spec. Then reference it in the TransferText method.

 

[Magnus1982]

Thank you everyone for help. All done !!!