VBA procedure to import csv file into access

Question

i need procedure in VBA to import data into access from csv excel file without some records,, as header and footer. Example,,, i have table in csv file, which contains some sentence which not belong table date

A1 this is some sentence title.......
A2 title
A3.......
A7 DATA DATA DATA DATA DATA
A8 rows DATA DATA DATA DATA DATA ...... ....
A256 DATA DATA DATA DATA
A257 this is some sentence

My Acess shoud contain only rows between A7 to A256. Does anyone knows procedure or whatever in VBA who solves my problems ?

thanks a lot

Edit

Can you clarify, please? Excel will open a CSV, but CSV means Comma Separated Value, and it is a text file. Do you have an Excel file or a text file? — Fionnuala, Jun 30 '12 at 15:40
I have pasted your data into your question. As you can see, it is not possible to tell where the lines are. You need to either choose to edit your post and repaste or tidy it up. The semi-colon (;) is not the standard delimiter for a csv. — Fionnuala, Jun 30 '12 at 17:52
It seems that this is a once-off and you are not completely familar with VBA, so I strongly recommend you try @Olivier Jacot-Descombes method first. — Fionnuala, Jun 30 '12 at 17:57
mistake in import table, bacause that create only 3 false columns, but my csv, have 11 separated with semicolon..but with this Oliver procedure separated comma...aand everthing is mistake — lama27, Jun 30 '12 at 18:17
example i have line: blue ; 10,090 ; 56,789 delimiter semicolon after this procedure blue10 / 09056 / 789 ...acess recognize comma as delimiter:( — lama27, Jun 30 '12 at 18:35

Olivier Jacot-Descombes · Answer 1 · 2021-03-17T19:58:24.680

17

The easiest way to do it is to link the CSV-file into the Access database as a table. Then you can work on this table as if it was an ordinary access table, for instance by creating an appropriate query based on this table that returns exactly what you want.

You can link the table either manually or with VBA like this

DoCmd.TransferText TransferType:=acLinkDelim, TableName:="tblImport", _
    FileName:="C:\MyData.csv", HasFieldNames:=true

Update

Dim db As DAO.Database

' Re-link the CSV Table
Set db = CurrentDb
On Error Resume Next:   db.TableDefs.Delete "tblImport":   On Error GoTo 0
db.TableDefs.Refresh
DoCmd.TransferText TransferType:=acLinkDelim, TableName:="tblImport", _
    FileName:="C:\MyData.csv", HasFieldNames:=true
db.TableDefs.Refresh

' Perform the import
db.Execute "INSERT INTO someTable SELECT col1, col2, ... FROM tblImport " _
   & "WHERE NOT F1 IN ('A1', 'A2', 'A3')"
db.Close:   Set db = Nothing

edited Mar 17 '21 at 19:58

answered Jun 30 '12 at 15:49

Olivier Jacot-Descombes

104,806
13
138
188

thanks, :) but i must writing VBA code, it is my task to find way how from excel csv file import data into access without some rows.I need some condition because required rows ..Not respond me, that i openinig imidiatly in aceess....several times a month i got csv file with approximately 2213 rows , and i must import into acess withoat some rows on begining and in the end – lama27 Jun 30 '12 at 16:09
2

This is exactly what I am telling you. Once the CSV-file is linked into access you can import the required rows with `INSERT INTO someTable SELECT col1, col2, ... FROM tblImport WHERE NOT F1 IN ('A1', 'A2', 'A3')` as an example. – Olivier Jacot-Descombes Jun 30 '12 at 16:33
1

The question is of whether your file is really a CSV file. In order to be a CSV-file it MUST contain the same number of columns in every row! If lines 1-3 and 257 contain some plain text, then this is NOT a CSV-File. – Olivier Jacot-Descombes Jun 30 '12 at 20:04
What's happening with `WHERE NOT F1 IN ('A1', 'A2', 'A3')`? – Martin F May 22 '15 at 00:54
The rows having the values "A1", "A2" or "A3" in the column `F1` are skipped, i.e. excluded from the result set. Note if the CSV file has field names in the first row and you import with `HasFieldNames:=true` then these names will be used as column names, otherwise Access assigns column names like `F1`, `F2`, `F3`... automatically. – Olivier Jacot-Descombes May 23 '15 at 14:56
What TransferType should be used for tab delimited file? – Sep 07 '19 at 13:12
When you perform the import manually, you can set a delimiter and other settings. Then you can store your entries as a named import specification. The `DoCmd.TransferText` command has an optional parameter for a specification name. See [Access VBA DoCmd.TransferText Method](https://access-excel.tips/access-vba-docmd-transfertext-method/) – Olivier Jacot-Descombes Sep 07 '19 at 15:26

Fionnuala · Answer 2 · 2012-06-30T22:00:35.863

Your file seems quite small (297 lines) so you can read and write them quite quickly. You refer to Excel CSV, which does not exists, and you show space delimited data in your example. Furthermore, Access is limited to 255 columns, and a CSV is not, so there is no guarantee this will work

Sub StripHeaderAndFooter()
Dim fs As Object ''FileSystemObject
Dim tsIn As Object, tsOut As Object ''TextStream
Dim sFileIn As String, sFileOut As String
Dim aryFile As Variant

    sFileIn = "z:\docs\FileName.csv"
    sFileOut = "z:\docs\FileOut.csv"

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set tsIn = fs.OpenTextFile(sFileIn, 1) ''ForReading

    sTmp = tsIn.ReadAll

    Set tsOut = fs.CreateTextFile(sFileOut, True) ''Overwrite
    aryFile = Split(sTmp, vbCrLf)

    ''Start at line 3 and end at last line -1
    For i = 3 To UBound(aryFile) - 1
        tsOut.WriteLine aryFile(i)
    Next

    tsOut.Close

    DoCmd.TransferText acImportDelim, , "NewCSV", sFileOut, False
End Sub

Edit re various comments

It is possible to import a text file manually into MS Access and this will allow you to choose you own cell delimiters and text delimiters. You need to choose External data from the menu, select your file and step through the wizard.

About importing and linking data and database objects -- Applies to: Microsoft Office Access 2003

Introduction to importing and exporting data -- Applies to: Microsoft Access 2010

Once you get the import working using the wizards, you can save an import specification and use it for you next DoCmd.TransferText as outlined by @Olivier Jacot-Descombes. This will allow you to have non-standard delimiters such as semi colon and single-quoted text.

my file is not small have with approximately 2213 rows, lines:) — lama27, Jun 30 '12 at 16:34
Where i this write , in acess ? New datebases,datebases tools, Visaul Basic...insert module?? really need help, i must finish this by monday — lama27, Jun 30 '12 at 16:39
It is MS Access VBA. If you do not know VBA, you will have problems, there is no error coding and you have not posted a sample of the text file, so I do not know if it is truly a CSV -- a comma delimited text file. Otherwise, the code is tested to run. — Fionnuala, Jun 30 '12 at 16:44
if you do not mind, i can send example my csv file to your mail, and i will see tath is csv file, and if you is not difficult...test code above, and tell me where i write ,, so that i can on my computer test code above :Sub StripHeaderAndFooter() Dim fs As Object ''FileSystemObject Dim tsIn As Object, tsOut As Object ''TextStream Dim sFileIn As String, sFileOut As String Dim aryFile As Varian — lama27, Jun 30 '12 at 16:55
No, I do not post my email on public sites, sorry. You can right-click on the file and choose Open With --> Wordpad. This is a text editor and you will see the actual contents of the file. Cut and paste the top few lines into your answer. To test the code, just paste it into an MS Access module and step through by pressing F8. If you have never used VBA before, you will need to do quite a lot of reading to get up to speed. — Fionnuala, Jun 30 '12 at 17:08

VBA procedure to import csv file into access

2 Answers2

Linked