Batch convert csv to xlsx

Dear Experts

In Access I to need to convert all the CSV files in a folder (and its sub folders) into xls or xlsx format and save then in a different place so they can be imported into the database I am building. This will be a regular task performed (if that makes a difference) ...

The folders in question are:

The top level folder for the CSVs to convert: C:\Users\Username\Desktop\Tap Forms\Import (there will be subfolders within this)
The destination / output folder is \Users\Username\Desktop\Tap Forms\Converted

Can anybody help?
Who is Participating?
Bill PrewConnect With a Mentor Commented:
Okay, here is a simple VBA code that should do that, just adjust the base path.

    Application.DisplayAlerts = False
    Application.StatusBar = True

    strWorkbook = ActiveWorkbook.Name
    strPath = "B:\EE\EE29023403\Files"
    strFile = Dir(strPath & "\*.csv")

    Do While strFile <> ""
        Application.StatusBar = "Converting: " & strFile
        Workbooks.Open Filename:=strPath & "\" & strFile
        ActiveWorkbook.SaveAs Filename:=Replace(strPath & "\" & strFile, ".csv", ".xls", compare:=vbTextCompare), FileFormat:=xlNormal
        strFile = Dir()

    Application.StatusBar = False
    Application.DisplayAlerts = True
End Sub

Open in new window

Bill PrewCommented:
Can you provide a sample of the CSV files?

correlateAuthor Commented:
They are all very different - they originate from a mac / iPhone database that has a CSV export facility and I want to access and update this on a PC, I was thinking this might be a case of "open file" > "save as" > move to next
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

If you're only going to reimport them, why do the conversion at all?  Why not just work with the CSV file?

PS, If I were going to do this, I would link to the .csv file using TransferText and then export as Excel using TransferSpreadsheet.  So two lines of code, no OLE automation and a couple more lines of code to set the file names and control the link loop.
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
From Access you can setup and save an import specification for text files (that includes csv). By doing it directly from Access and importing directly into access using that saved import spec, you ensure that all times, comma, semicolons and text is imported consistently. Otherwise you are relying on excels "best guess" as to how the csv file is to be read. It MIGHT be good enough, but I would recommend importing directly in a controlled way.
Bill PrewCommented:
I did miss the fact that you want a recursive search for all files in all subfolders.  That can be added without too much trouble, but before I do that I'll wait to hear what your comments are to my first code, and also some of the other thoughtful comments that have been added.

David AndersTechnician Commented:

Check “Move files to another folder after processing” option and select the Processed folder clicking on the little folder icon. FileSculptor will process all files and move then to the Processed folder. With this configuration, when there are new files to be processed, put the files in the folder C:\Convert\Input and run the conversion project again.
correlateAuthor Commented:
brilliant, thank you
Bill PrewCommented:
Great, glad that helped.

OK, I'm sure it makes some sense to someone to open a list of .csv files with Excel and save them as .xls files, just not to me.  One thing to be VERY conscious of if you want to copy this procedure is if your .csv files contain long numbers (10 + digits)  or numeric codes with leading zeros such as zip codes or SSN, Excel will corrupt those fields (long numbers get saved in scientific notation and leading zeros are stripped from zip codes and SSN's) whereas if you simply imported the .csv files with Access using an import spec, you could prevent the data from being corrupted.

To properly open a .csv file in Excel, DO NOT double-click on it.  Open Excel, and use the Data tab to import the .csv file.  This will allow you to create an import spec.  Unfortunately, Excel doesn't save the import specs so this becomes a tedious method if you have to do it for multiple files.  Excel thinks it is smarter than us  but it is not.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.