?
Solved

Batch convert csv to xlsx

Posted on 2017-05-17
10
Medium Priority
?
504 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 57

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 57

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 38

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 8

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 57

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 9

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 57

Expert Comment

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


»bp
0
 
LVL 38

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 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