CODEWITHSUNDEEP
excelcsvcmdvba
user3906930
user3906930

Reputation: 77

Combining CSV Files into one master file and keep the file name in the Column 2

I have many CSV. files that I want to combine into one big one, further to that, I also need to keep the File Name in Column 2.

Contents of my CSV file

File Name ABNK102455

Column A
12/215425
12/125485
12/215435

File Name ABNK102456

Column A
12/215425
12/125485
12/215435

Result

Combined.CSV

Column A
12/215425   ABNK102455
12/125485   ABNK102455
12/215435   ABNK102455
12/215425   ABNK102456
12/125485   ABNK102456
12/215435   ABNK102456

Is this possible, and How?

Upvotes: 0

Views: 2747

Answers (2)

Magoo
Magoo

Reputation: 79982

@ECHO OFF
SETLOCAL
SET "sourcedir=U:\sourcedir"
SET "mask=q*.csv"
SET "headerlinegenerated="
(
 FOR /f "delims=" %%a IN ('dir /a-d /b "%sourcedir%\%mask%"') DO (
  SET "headerline="
  FOR /f "usebackqdelims=" %%m IN ("%sourcedir%\%%~nxa") DO (
   IF NOT DEFINED headerlinegenerated SET "headerlinegenerated=Y"&ECHO %%m
   IF DEFINED headerline ECHO %%m %%~na
   SET headerline=Y
  )
 )
)>newfile.txt
GOTO :EOF

You would need to change the setting of sourcedir to suit your circumstances.

I used a files named q26135599-1.csv and q26135599-2.csv containing your data for my testing. You would need to adjust the setting of mask to suit.

Produces newfile.txt

Best not to try to create newfile.txt as a .csv within your source directory, unless you're really sure that it won't be included in %mask%.

Upvotes: 3

MikeD
MikeD

Reputation: 8941

To get you going ....

Sub Test()
Dim Idx As Long
Dim RowNum As Long
Dim TargetRange As Range
Dim FileName As String

    RowNum = 1
    Set TargetRange = ActiveSheet.[A1]

    ' File selection loop
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        ' use .Filters.Add ... to present only .txt, .csv files etc.
        .Show

        ' File processing loop
        For Idx = 1 To .SelectedItems.Count

            ' here you get each file name inc. full path ... one by one
            FileName = .SelectedItems(Idx)

            ' isolate filename
            ' open file

            ' while not end of file
            '     read from file line by line into a string variable
            '     place string into TargetRange(RowNum, 1)
            '     place filename into TargetRange(RowNum, 2)
            '     increment RowNuM

        Next Idx
    End With
End Sub

Upvotes: 0

Related Questions