Solved

Batch convert csv to xlsx

Posted on 2017-05-17
10
50 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 54

Expert Comment

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


»bp
0
 

Author Comment

by:correlate
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 54

Accepted Solution

by:
Bill Prew earned 500 total points
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
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!

 
LVL 37

Expert Comment

by:PatHartman
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)
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 54

Expert Comment

by:Bill Prew
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
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
brilliant, thank you
0
 
LVL 54

Expert Comment

by:Bill Prew
Great, glad that helped.


»bp
0
 
LVL 37

Expert Comment

by:PatHartman
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

Independent Software Vendors: 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!

Join & Write a Comment

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

737 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