Network Automation Software We are currently migrating this forum
over to our HelpSystems domain. Please
post all new threads in our new
HelpSystems Community Portal.
Post to the HelpSystems Forum
You are not currently logged on. You must be logged on in order to post. Log on
Or Create a new account
AutoMate Discussion
Decrease font size
Increase font size
Topic Title: Convert xls file to csv
Topic Summary:
Created On: 01/13/2010 09:58 AM
Status: Read Only
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
View similar topics View similar topics
View topic in raw text format. Print this topic.
Answer This question was answered by JeanDelfosse, on Wednesday, February 17, 2010 12:32 PM

Answer:
Matthias,
hereunder a basic script that does it perfectly
You may have to trim the #reference line to fit your own Excel installation
 01/13/2010 09:58 AM
User is offline View Users Profile Print this message

Author Icon
MatthiasSpitzhorn
Journeyman (50-99)

Posts: 71
Joined: 07/27/2005

Hi,

i have a problem, i get every day from one of our customer an excel sheet.

This sheet i want to convert to an csv file.

How can i do this with automate.

Thanks for your help.


 Category Survey
 01/13/2010 12:13 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Answer Answer
Matthias,
hereunder a basic script that does it perfectly
You may have to trim the #reference line to fit your own Excel installation

 Category Survey
AutoMate 7 version:
Windows version:



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 10/29/2010 02:39 PM
User is offline View Users Profile Print this message

Author Icon
TimHicks
n00b (>6 posts)

Posts: 4
Joined: 10/29/2010

Jean, 

I found your code for AutoMate 6 and it works for first worksheet in a workbook.

I have a workbook with 6 worksheets that I want to convert each sheet to a separate .csv file.  I would like to name each file using the Sheet name from the original .xls file.

Thanks for the site to post this question.

Tim



 Category Survey
 10/29/2010 11:20 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Tim,
the attached bas file converts each sheet to a csv
Just rename it to eliminate the .txt

 Category Survey
AutoMate 7 version:
Windows version:





-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 11/01/2010 08:42 AM
User is offline View Users Profile Print this message

Author Icon
TimHicks
n00b (>6 posts)

Posts: 4
Joined: 10/29/2010

Jean,

That worked great, Thanks

My Worksheet has some date columns, when I convert a sheet to a csv manually the Month and Day fields use 2 digits ("01/09/2010")

I have the "Short date" as mm/dd/yyyy in the Regional Options.

This VB does not seem to be using those settings I get ("1/9/2010"), any idea on how to force the settings ?

Tim



 Category Survey


Edited: 11/01/2010 at 08:50 AM by TimHicks
 11/01/2010 09:54 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

It's the same in my system.
You can force the date format by using a statements like
Set objRange = objXlWorksheet.Range("A1","C20")
objRange.NumberFormat = ("mm/dd/yyyy")

after
objXlWorksheet.Activate

where A1:C20 is the range which you want to convert, but if there are other numbers in the cells, they will also be converted
e.g. 6 willl yield 01/06/1900
This must be done selectively for the real date cells.

You must know upfront where the dates are located, which is not easy.
That must also be feasible programmatically, but I don't have time now to do it.

 Category Survey
AutoMate 7 version:
Windows version:


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 06/24/2014 07:11 AM
User is offline View Users Profile Print this message

Author Icon
ElginGreen
Student (5-19 posts)

Posts: 6
Joined: 06/20/2005

I know this is an old post, but I found an error in the code. It leaves an orphaned Excel process every time it runs:

Sub Main
1 StrXLSFile="c:\temp\MyXLS.xls"
2 StrCSVFile="c:\temp\MyCSV.csv"
3 Dim objXlApp As New Excel.Application
4 Dim objXlBook As Excel.Workbook
5 Set objXlBook = Excel.Workbooks.Open( StrXLSFile)
6 objXlApp.DisplayAlerts = False
7 objXlBook.SaveAs StrCSVFile, xlCSV
8 objXlBook.Close SaveChanges:=True
9 objXlApp.Quit
10 Set objXlBook = Nothing
11 Set objXlApp = Nothing
End Sub
...

Line 3 (objXlApp) dims the Excel object reference, but the line 5 does not use that object. Instead it directly calls Excel to set the workbook (Set objXlBook = Excel...). At this point the script loses its reference to the original Excel object reference. Later, when line 11 attempts to set the Excel object to nothing, it fails.

Fix:
I changed line 5 to

Set objXlBook = objXlApp.Workbooks.Open(StrXLSFile)

All object references are intact...no orphan Excel process.


 Category Survey
AutoMate 7 version:
Windows version: Windows 7
Statistics
18258 users are registered to the AutoMate Discussion forum.
There are currently 0 users logged in.
The most users ever online was 5551 on 01/08/2018 at 11:11 AM.
There are currently 1427 guests browsing this forum, which makes a total of 1427 users using this forum.

FuseTalk Enterprise Edition v4.0 - © 1999-2020 FuseTalk Inc. All rights reserved.

Sitemap Network Automation Software Blog