?
Solved

Batch convert csv to xlsx

Posted on 2017-05-17
10
Medium Priority
?
1,269 Views
Last Modified: 2017-05-19
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?
0
Comment
Question by:correlate
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 59

Expert Comment

by:Bill Prew
ID: 42139336
Can you provide a sample of the CSV files?


»bp
0
 

Author Comment

by:correlate
ID: 42139343
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
0
 
LVL 59

Accepted Solution

by:
Bill Prew earned 2000 total points
ID: 42139374
Okay, here is a simple VBA code that should do that, just adjust the base path.

Sub CSV2XLS()
    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
        ActiveWorkbook.Close
        Windows(strWorkbook).Activate
        strFile = Dir()
    Loop

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

Open in new window


»bp
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Expert Comment

by:PatHartman
ID: 42139416
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.
0
 
LVL 10

Expert Comment

by:Anders Ebro (Microsoft MVP)
ID: 42139930
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.
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 42140819
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.


»bp
0
 
LVL 10

Expert Comment

by:davidanders
ID: 42141258
https://www.filesculptor.com/batch-convert-csv-to-excel/

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.
0
 

Author Closing Comment

by:correlate
ID: 42142491
brilliant, thank you
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 42142502
Great, glad that helped.


»bp
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 42142732
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
What we learned in Webroot's webinar on multi-vector protection.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question