Sign in to follow this  
Followers 0
Chad

Import CSV File to Existing Workbook

14 posts in this topic

Greetings everyone,

This is a first post for me, although I've been casually using AutoIt for a while now. The forum has been a huge resource to me.

To the point, I'm trying to automate the conversion of a wide array of text files to output csv files. I work in a company that utilizes data feeds from online retailers, and since each seller has a unique layout of their data, the conversion process rearranges that layout to a unified scheme.

A select few feeds (anywhere between 20 and 100) require special 'converters' that we've created in Excel. There is an Input sheet, where the downloaded feed (CSV) file is imported, and the Conversion sheet simply calls data from Input to reorder the columns. It's a simple approach, but it works very well given that most feeds we work with don't exceed the Excel row limit.

Generally these Excel conversions have been done manually, however now that I am much more familiar with AutoIt I would like to automate that process. So far I have written a program that downloads the feeds, and then converts them from other formats to CSV. The last step is to import that CSV into the Input sheet so I can proceed with the conversion process.

I am currently using Microsoft Excel COM UDF library for AutoIt v3, v1.4, for the bulk of the Excel automations. I have tried numerous approaches, including reading the CSV file into an array and writing that array to the Input sheet (which takes a lot of time). It would be much faster if I could just import the file automatically.

I would greatly appreciate any useful advice in this regard.

Thank you in advance,

Chad

Share this post


Link to post
Share on other sites



Hello and welcome to the forums!

For specific things like this, I find it's easiest to just start a macro record in Excel, then go through the motions of importing your csv then once it's all imported stop the macro. Get the macro text and convert it to AutoIt. Takes a little trial and error if you're not used to working with VBA and/or Obj in AutoIt. I think some functions you find in a macro aren't scriptable but this shouldn't be a problem...

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Hello and welcome to the forums!

For specific things like this, I find it's easiest to just start a macro record in Excel, then go through the motions of importing your csv then once it's all imported stop the macro. Get the macro text and convert it to AutoIt. Takes a little trial and error if you're not used to working with VBA and/or Obj in AutoIt. I think some functions you find in a macro aren't scriptable but this shouldn't be a problem...

Thanks for the reply. The resulting Macro source reads as:

_________________________________________________________________________________________

With ActiveSheet.QueryTables.Add(Connection:= _

"TEXT;C:\test.csv" _

, Destination:=Range("$A$1"))

.Name = "test"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 437

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = False

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = True

.TextFileSpaceDelimiter = False

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With

_________________________________________________________________________________________

Now I've tried using VB script before in AutoIt, based on what I saw in the above referenced COM include. I notice the differences between what I had originally tried and what's shown here, so that feels like progress (and that the VB Code works instantly).

My next question is, what is the best way to convert this into AutoIt? I've looked for specific articles on the topic, but no luck.

Currently the function requiring this component starts out with:

_________________________________________________________________________________________

;declaration of basic input values such as converter file location would go in this section, omitting

$nExcel = _ExcelBookOpen($Converter,1)

_ExcelSheetActivate($nExcel,"Input")

[Where the converted VB script would go]

_ExcelBookSave($nExcel)

_ExcelSheetActivate($nExcel,"Conversion")

With $nExcel

.ActiveSheet.SaveAs($oFileName) ;saving the conversion sheet as the converted file

Endwith

$nExcel = 0

_ExcelBookClose($nExcel)

_________________________________________________________________________________________

The reason for including this is that I want to make sure the code context is appropriate (for example, the saving of the conversion sheet doesn't seem to work even when I skip the text import part, and I'm not sure if I'm fitting it into rest of the function correctly).

Chad

Edited by Chad

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Another option, using less AutoIt, is to save the macro in that workbook then use the Excel functions in AutoIt to open the Excel spreadsheet, run that macro, save spreadsheet as something else, then...lather, rinse, repeat. When you create the macro in the first place it asks where to save it, make it "This Workbook" which I think is default anyway.

Here's a piece that might work for you...using AutoIt ExcelCOM to import the CSV as opposed to using AutoIT to kick off a macro. See which works best, or consider jchd's suggestion following this post.

#include <Excel.au3>

$oExcel = _ExcelBookNew() ;Replace this with the line that opens your special workbook, instead of creating a new workbook, might want to make sure the proper sheet is activated first just in case

$sCSVFile = "C:\test.csv"

With $oExcel.ActiveSheet
    .QueryTables.Add("TEXT;" & $sCSVFile, $oExcel.ActiveSheet.Range("$A$1"))
    .QueryTables(1).TextFileCommaDelimiter = True
    .QueryTables(1).Refresh
EndWith
Edited by MrMitchell
1 person likes this

Share this post


Link to post
Share on other sites

Maybe I miss obvious aspects of your problem, but why not process the whole lot of various text files by AutoIt itself and output your .csv (or interface a database directly) from there in a self-contained script?

You might even get more efficiency by importing your various text files into an SQLite (temporary or permanent) DB and producing consistent .CSV or enterprise DB engine interface for later use in your chain.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Another option, using less AutoIt, is to save the macro in that workbook then use the Excel functions in AutoIt to open the Excel spreadsheet, run that macro, save spreadsheet as something else, then...lather, rinse, repeat. When you create the macro in the first place it asks where to save it, make it "This Workbook" which I think is default anyway.

Here's a piece that might work for you...using AutoIt ExcelCOM to import the CSV as opposed to using AutoIT to kick off a macro. See which works best, or consider jchd's suggestion following this post.

#include <Excel.au3>

$oExcel = _ExcelBookNew() ;Replace this with the line that opens your special workbook, instead of creating a new workbook, might want to make sure the proper sheet is activated first just in case

$sCSVFile = "C:\test.csv"

With $oExcel.ActiveSheet
    .QueryTables.Add("TEXT;" & $sCSVFile, $oExcel.ActiveSheet.Range("$A$1"))
    .QueryTables(1).TextFileCommaDelimiter = True
    .QueryTables(1).Refresh
EndWith

Results in:

==> The requested action with this object has failed.:

.QueryTables(1).Refresh

.QueryTables(1).Refresh^ ERROR

Possible reasons?

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

Results in:

==> The requested action with this object has failed.:

.QueryTables(1).Refresh

.QueryTables(1).Refresh^ ERROR

Possible reasons?

To be honest couldn't tell you, don't know too much about QueryTables. Might have something to do with how your sheet is set up, the example I provided was very very simple and worked on a brand new spreadsheet. Have you given thought to jchd's suggestion or possibly just using AutoIt to run your Excel macro?

Trying throwing this in just after .QueryTables.Add and before .QueryTables(1) to see how many are there:

MsgBox(0, "", $oExcel.ActiveSheet.QueryTables.Count)

Edit: Above won't work, sorry. Comment out everything except .QueryTables.Add then place above line after the With/EndWith:

With $oExcel.ActiveSheet
    .QueryTables.Add("TEXT;" & $sCSVFile, $oExcel.ActiveSheet.Range("$A$1"))
    ;.QueryTables(1).TextFileCommaDelimiter = True
    ;.QueryTables(1).Refresh
EndWith

MsgBox(0, "", $oExcel.ActiveSheet.QueryTables.Count)
Edited by MrMitchell

Share this post


Link to post
Share on other sites

Maybe I miss obvious aspects of your problem, but why not process the whole lot of various text files by AutoIt itself and output your .csv (or interface a database directly) from there in a self-contained script?

You might even get more efficiency by importing your various text files into an SQLite (temporary or permanent) DB and producing consistent .CSV or enterprise DB engine interface for later use in your chain.

I would actually prefer it. However, since the conversion work has already been taken care of in the Excel files, I would prefer to use that work and build on it rather than recreating the conversions in AutoIt. In particular many of the conversions can be fairly complicated depending on how 'complete' the input data may be. Not impossible of course, but very time-consuming.

The DB engine solution sounds interesting, but a little outside my knowledge. Can you provide references?

Thanks,

Chad

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

To be honest couldn't tell you, don't know too much about QueryTables. Might have something to do with how your sheet is set up, the example I provided was very very simple and worked on a brand new spreadsheet. Have you given thought to jchd's suggestion or possibly just using AutoIt to run your Excel macro?

Trying throwing this in just after .QueryTables.Add and before .QueryTables(1) to see how many are there:

MsgBox(0, "", $oExcel.ActiveSheet.QueryTables.Count)

AutoIt running the macro is sounding more like the best solution in this case. I'll post back when I have that figured out, but meanwhile thank you for your help.

EDIT: Tried the MsgBox line, returns a value of 1.

Edited by Chad

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

Google for SQLite _and_ read AutoIt help file about SQLite. Chime here or PM for more.

I was expecting your 'conversion code already working' objection, but this way you have a separate program (or bunch of spreadsheet formulas, which is the same) to maintain.

I've seen big-names companies go thru horror-stories by relying on Excel or Access. One case finally cost more than $350K just for _one_ Excel spreadsheet (of vital importance).

It would be interesting to have a rough idea of what your basic/typical/pathalogical conversion fonctions look like.

Could you post a small set of input/ouput examples (masquerade private data if needed) and a sample sketch of transform rules.

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

One case finally cost more than $350K just for _one_ Excel spreadsheet (of vital importance).

Umm.... OUCH!

Share this post


Link to post
Share on other sites

Google for SQLite _and_ read AutoIt help file about SQLite. Chime here or PM for more.

I was expecting your 'conversion code already working' objection, but this way you have a separate program (or bunch of spreadsheet formulas, which is the same) to maintain.

I've seen big-names companies go thru horror-stories by relying on Excel or Access. One case finally cost more than $350K just for _one_ Excel spreadsheet (of vital importance).

It would be interesting to have a rough idea of what your basic/typical/pathalogical conversion fonctions look like.

Could you post a small set of input/ouput examples (masquerade private data if needed) and a sample sketch of transform rules.

It's fairly simple, but to illustrate: Usually two sheets [input][Conversion]. Text feed gets imported into input sheet, and in the output sheet cell A1 the formula is =Input!C1. So that maps C1 of the input feed to A1 of the converted file. Map the remaining columns, repeat across rows as necessary. Then save [Conversion] as a CSV, which gets inserted into the server (which then treats each row an an individual product record).

In many instances, the formula uses the data from Input!CCRR as a condition in a VLOOKUP function, or combines multiple columns from Input into a single string. This method has been done by hand for several months now, and has been astoundingly effective for such a simple and somewhat rudimentary fix. However I am certainly interested in any potential issues that we haven't yet seen/considered but might arise.

I dug around a bit for VBA coding, and the complete code for my sought-after solution is as follows (also, discovered that naming the macro 'Auto_Open' kicks off the macro when the Excel book is opened):

Sub Auto_Open()

'

' Auto_Open Macro

'

'

Sheets("Input").Select

Range("A1:CA60000").Select

Selection.ClearContents

Range("A1").Select

With ActiveSheet.QueryTables.Add(Connection:= _

"TEXT;C:\test" & Format(Now(), "yyyymmdd") & ".csv" _

, Destination:=Range("$A$1"))

.Name = "test"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 437

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = False

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = True

.TextFileSpaceDelimiter = False

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

Sheets("Conversion").Activate

ActiveWorkbook.SaveAs Filename:="C:\test" & Format(Now(), "yyyymmdd") & ".csv", _

FileFormat:=xlCSV, CreateBackup:=False

End With

End Sub

On a side note, I have looked into recreating the converter files programatically; reading and parsing the input text file into a multidimensional array, reading a 'transcription' file into an array containing rules for column rearrangement and data manipulation, and then writing the rearranged array to an output file. However, the process ended up being a bit slow, and the biggest double-hurdle was designing a rules key that could encompass all the conditions I need to cover (everything from simply calling the value in the input sheet to replacing it with a text string, to returning a shipping cost from yet another separate table). While I would prefer this method overall, I'm still new enough at coding in general that this would extend the dev period beyond what is currently practical.

With regards to the above VB code, I've run it a few times now, and it works very quickly without any errors.

Share this post


Link to post
Share on other sites

So after inserting the above VB code into the converter, I then used the following code in AutoIt to run the macro. I'm pleased to say that the conversion worked perfectly. I can now insert the macro into all the existing converters and run all the conversions serially.

$oExcel = ObjCreate("Excel.Application")
    $oExcel.WorkBooks.Open($ConverterLocation)
    $oExcel.Application.Run("Auto_Open")
    _ExcelBookClose($oExcel,0)
    sleep(200)
    send("n")
    $oExcel = 0

Thanks to everyone for your help in resolving this issue. I can now complete this portion of the chain, run through several trials, and begin moving the converted files to our FTP server. :)

Share this post


Link to post
Share on other sites

I was certainly not implying you _would_ get into dead ends using this, but from what you describe there's nothing that seamingly can't be done very efficiently into a single AutoIt program.

I bet it wouldn't be a daunting task to read the input except the first two rows, eventually perform a lookup on a mixed potatoes of some columns in particular cases of input source, and feed the result directly to the server DB using an ODBC connector or simply produce a .CSV just like what you're doing right now.

This way you would only rely on a single code where all your input filtering/validation/conversion/whatever would reside. But take this a my own humble opinion.

Anyway and unles/until you centralize your conversion process, you can still automate it with the _Excel functions to some extent but I'll leave that part to more knowledgeable people.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0