Import CSV File to Existing Workbook

[Chad]

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

[MrMitchell]

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

[Chad]

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 February 22, 2011 by Chad

[MrMitchell]

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 February 23, 2011 by MrMitchell

[jchd]

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.

[Chad]

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?

[MrMitchell]

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 February 23, 2011 by MrMitchell

[Chad]

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

[Chad]

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 February 23, 2011 by Chad

[jchd]

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 February 23, 2011 by jchd

[MrMitchell]

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

Umm.... OUCH!

[Chad]

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.

[Chad]

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.

[jchd]

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.