CODEWITHSUNDEEP
vbaexcelcsvms-access
Besarion
Besarion

Reputation: 149

Combining CSV files from one folder into one file through MS Acces s vba

Hi there so I finished the section of a program which calculates and exports a csv with results. (ends up about 1600 csv files) each having only 1 column and between 20 and 0 rows. I would like my MS Access VBA program to join them together into one larger CSV. So Same header only once at the top of the new file.

The program i have so far seems to fall over at the part where it tries to import the Reg. Number of the File. 

Dim db As DAO.Database
Set db = CurrentDb
MTH = Format(Date, "mmm")
UserInput = InputBox("Enter Country Code")
Dim strSourcePath As String
Dim strDestPath As String
Dim strFile As String
Dim strData As String
Dim x As Variant
Dim Cnt As Long
Dim r As Long
Dim c As Long
Dim wks As Excel.Worksheet

    Application.Echo False

    'Change the path to the source folder accordingly
    strSourcePath = "Q:\CCNMACS\AWD" & CTRY

    If Right(strSourcePath, 1) <> "\" Then strSourcePath = strSourcePath & "\"

    'Change the path to the destination folder accordingly
    strDestPath = "Q:\CCNMACS\AWDFIN"

    If Right(strDestPath, 1) <> "\" Then strDestPath = strDestPath & "\"

    strFile = Dir(strSourcePath & "*.csv")

  Do While Len(strFile) > 0
        Cnt = Cnt + 1
        If Cnt = 1 Then
            r = 1
        Else
            r = Cells(Rows.Count, "A").End(xlUp).Row + 1
        End If
        Open strSourcePath & strFile For Input As #1
            If Cnt > 1 Then
                Line Input #1, strData
            End If
            Do Until EOF(1)
                Line Input #1, strData
                x = Split(strData, ",")
                For c = 0 To UBound(x)
                    wks.Cells(r, c + 1).Value = Trim(x(c)) 'Error is here: Run time error '91': Object variable or With Block variable not set
                Next c
                r = r + 1
            Loop
        Close #1
        Name strSourcePath & strFile As strDestPath & strFile
        strFile = Dir
    Loop

    Application.Echo True

    If Cnt = 0 Then _
        MsgBox "No CSV files were found...", vbExclamation

Upvotes: 0

Views: 1502

Answers (2)

ASH
ASH

Reputation: 20302

This should do what you want.

Sub Import()

        Dim strPathFile As String, strFile As String, strPath As String
        Dim strTable As String
        Dim blnHasFieldNames As Boolean

        ' Change this next line to True if the first row in EXCEL worksheet
        ' has field names
        blnHasFieldNames = True

        ' Replace C:\Documents\ with the real path to the folder that
        ' contains the EXCEL files
        strPath = "C:\your_path_here\"

        ' Replace tablename with the real name of the table into which
        ' the data are to be imported
        strTable = "Table1"

        strFile = Dir(strPath & "*.csv")
        Do While Len(strFile) > 0
              strPathFile = strPath & strFile
              DoCmd.TransferText acImportDelim, "", strTable, strPathFile, blnHasFieldNames

        ' Uncomment out the next code step if you want to delete the
        ' EXCEL file after it's been imported
        '       Kill strPathFile

              strFile = Dir()
        Loop

End Sub

enter image description here

enter image description here

See the links below for additional details pertaining to this topic.

https://anthonysmoak.com/2018/04/10/how-to-fix-an-import-specification-error-in-microsoft-access/

https://www.oakdome.com/programming/MSAccess_ExportSpecifications_TransferText_To_CSV.php

Upvotes: 0

ashleedawg
ashleedawg

Reputation: 21639

Your question isn't absolutely definitive as to what you're trying to do, but if I understand correctly, you just need to append several files to the end of each other, to make "one big CSV".

If that's true then there are several ways to do this a lot simpler than using VBA. .CSV files are just plain text files with comma's separating each field, and a .CSV filename extension.

Personally I would use Notepad++ (I assume it's capable of this; it does everything else), or perhaps even easier, I would use the Windows Command Prompt.

Let's say you have a folder with files:

File1.csv
File2.csv
File3.csv
...etc

  • Open the Windows Command Prompt. (One way is with the Windows KeyWindows key + R, then type cmd and hit Enter.)

  • Change directory with to the file location using cd (same as ChDir).
    (For example, you might use cd c:\users\myFolder, and then hit Enter)

  • To combine all CSV's in the folder into one, you could use a command like:

    copy *.csv combinedfile.csv

That's it!

A file is created named combinedfile.csv. You can open in Excel or a text editor (like Notepad) to double-check it and adjust manually if necessary.

Obviously there are many ways you could vary the command, like if you only wanted the files that start with the word File you could use:

copy file*.csv combinedFile.csv

img

Upvotes: 5

Related Questions