?
Solved

Batch convert csv to xlsx

Posted on 2017-05-17
10
Medium Priority
?
864 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 39

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 9

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 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 57

Expert Comment

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


»bp
0
 
LVL 39

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

649 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