How to import cvs files into excel

[Mr. Crimson]

You're right. I tried with my home system which is Excel 2003 it can't display any data greater than 65535 rows. I think I had mistaken as there isn't any error message pop-up for that.  :">

Now I'm trying to figure out how to change that macro and script to suit my path and filename to test. Thanks randallc.

<{POST_SNAPBACK}>

You can have more than 65536 lines, but you won't be able to display more than that number at a time.

Exactly how many lines would all of the combined files contain?

-Crim

[igmeou]

I need someone to explain this part of the code.

ChDir "C:\Program Files\AutoIt3"
    ActiveWorkbook.Save
    'Filename:="C:\Program Files\AutoIt3\book1.xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

When I remove the the " ' " b efore the Filename=:... it give me error. How would I be able to save the file into a different location?

Do I need to do this on every worksheet to import everyone?

How to make my filename to be imported dynamically as I need to to import everynow n then with different filename (normally in the form of date)?

For the au3 file this is the error msg.

C:\Documents and Settings\...\ExcelCom.au3 (207) : ==> The requested action with this object has failed.: 
.Application.Run($ExcelValue) 
.Application.Run($ExcelValue)^ ERROR

I suspect the problem is I don't understand this line of code.

_XLmacroRun($FilePath,1,[B]"persoNAL.XLS!ImportCSV"[/B])

I don't know what should I fill for this. And I don't think I have the personal.xls file around when I tried to search.

Note: I'm using office2003 now to test and the final script will be running on office2K.

Waoh! That's quite a lengthy one... Can someone pleaes enlighten me please.

You can have more than 65536 lines, but you won't be able to display more than that number at a time.

Exactly how many lines would all of the combined files contain?

I really don't know whats the total length. The file can be as low as a few lines to I don't know how many lines... Normally, I would set auto filter to read the imported files. Edited September 12, 2005 by igmeou

[randallc]

I suspect the problem is I don't understand this line of code.

_XLmacroRun($FilePath,1,[B]"persoNAL.XLS!ImportCSV"[/B])

You will see my statement;

["Here is macro recorded as

"ImportCSV"

in hidden "Personal.xls" sheet, started from startup directory of Excel

"]

You need to put this macro (or record your own) in a new worksheet personal.xls, and save it to your startup excel directory (usually in C:\Program Files\Microsoft Office11\OFFICE11\XLSTART)

Do I need to do this on every worksheet to import everyone?

How to make my filename to be imported dynamically as I need to to import everynow n then with different filename (normally in the form of date)?

Probably best NOT to change the Excel macro, once you can call it, and simply copy a blank sheet into book1.xls in autoit directory (or change the place, then use AutoIt script to save it as you wish to place and name once it has run?)

For the au3 file this is the error msg.

C:\Documents and Settings\...\ExcelCom.au3 (207) : ==> The requested action with this object has failed.:

My ExcelCOM.au3 has bugs; but you need it in your autoit3/ include directory; is that where you have it?

Randall

[igmeou]

Did what u had mention but the error is still the same.

But I don't have the personal.xls file as default. I just copy a xls file over and rename it. I having the marcro named as "persoNAL.xls!Sheet1.ImportCSV" does that matter/affect the script?

[randallc]

Yes,

You need the macro call to match how the macro name appears in "tools/ macro"; you may, then, have it simply as "ImportCSV" there in your imported macro?

Can you show more of the exact

1. AutoIt script.

2. Excel macro

3. Are you able to run other ExcelCom.au3 commands? (eg simply read and show a worksheet?)

you are using?

Thanks, Randall

[igmeou]

This is my au3 script

#include<ExcelCom.au3>
; none of these shorter commands are visible until you say "_XLshow"
dim $FilePath,$var1,$LastRow
$FilePath="C:\Documents and Settings\Administrator\Desktop\ProxyTemplate1.xls"
If Not FileExists($FilePath) Or Not StringInStr($FilePath, "xls") Then
  $FilePath = FileOpenDialog("Go - Choose your excel file as inbuilt one not exists", $FilePath, "Worksheet" & " (" & "*.xls" & ")", 1)
EndIf
$Time1=@HOUR&" hour:"&@MIN&" @MIN:"&@sec&" @sec:"
_XLmacroRun($FilePath,"Sheet1","persoNAL.XLS!ImportCSV")
_XLshow($FilePath,1)
$Time2=@HOUR&" hour2:"&@MIN&" @MIN2:"&@sec&" @sec2:"
MsgBox(0,"_XLmacroRun=","$Time1="&$Time1&@CRLF&"$Time2="&$Time2)

I attached my excel file below.

Ops file too big can't attach. Below is the modified of your Macro.

CODE

Sub ImportCSV()

'

' ImportCSV Macro

' Macro recorded 12/09/2005 by Randall Clapp

'

'

With ActiveSheet.QueryTables.Add(Connection:= _

"TEXT;C:\Documents and Settings\Administrator\Desktop\gzip\20050905", Destination:=Range( _

"A1"))

.Name = "fiftywideBy6"

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

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = False

.TextFileSpaceDelimiter = True

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _

1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With

Application.CommandBars("External Data").Visible = False

ChDir "C:\Documents and Settings\Administrator\Desktop\gzip\"

Workbook.Save

'Filename:="C:\Documents and Settings\Administrator\Desktop\gzip\book1.xls", _

FileFormat:=xlNormal, Password:="", WriteResPassword:="", _

ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

In the Tools -> Macro it is name as "persoNAL.XLS!Sheet1.ImportCSV"

Sorry that I can't provide the data file as it is too big and confidential...

But it is a space delimited file with filename as "20050905" no extension, and everday I would have 1 file for that.

I'm using autoit-v3.1.1.73-beta, office 2003 on a winXP pro system.

Is the information sufficient?

Edited September 13, 2005 by igmeou

[randallc]

In the Tools -> Macro it is name as "persoNAL.XLS!Sheet1.ImportCSV"

Maybe OK, but probably not if you named it that way! Excel will be looking for a hidden ("!") macro in a hidden worksheet called "persoNAL.XLS".

Should you just rename it as "ImportCSV", then call it that name from AutoIt, or do I mis-understand you?

2. do you have the macro every time in an empty "ProxyTemplate1.xls"?

Randall

Edited September 13, 2005 by randallc

[randallc]

3. Are you able to run other ExcelCom.au3 commands? (eg simply read and show a worksheet?)

?

Randall

[randallc]

  ActiveWorkbook.Save

    'Filename:="C:\Program Files\AutoIt3\book1.xls", _

        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _

        ReadOnlyRecommended:=False, CreateBackup:=False

The command , and you wil se if you record yourself, was originally

  ActiveWorkbook.SaveAs Filename:="C:\Program Files\AutoIt3\book1.xls", _

        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _

        ReadOnlyRecommended:=False, CreateBackup:=False

, but I prefer to just "Save" to the same each time, then copy or use blank from autoIt?

Randall

[igmeou]

Maybe OK, but probably not if you named it that way! Excel will be looking for a hidden ("!") macro in a hidden worksheet called "persoNAL.XLS".

Should you just rename it as "ImportCSV", then call it that name from AutoIt, or do I mis-understand you?

2. do you have the macro every time in an empty "ProxyTemplate1.xls"?

Randall

I think I don't understand your explaination...

I'm using the Tools->Macro->Visual Basic Editor on the excel file named "persoNAL.XLS". Then on in the editor, I go under the Sheet1 and paste your code there. After I save that it will produce a macro name "persoNAL.xls!Sheet1.ImportCSV"

by default. What do you mean by macro in a hidden worksheet called "persoNAL.XLS"? Is this the excel filename or a hidden worksheet named "persoNAL.XLS"?

Oh the ImportCSV is a just the subroutine that I paste into the editor.

For 2., it is the same copy of the "persoNAL.XLS" that I copy manually myself to the desktop for testing.

I haven't tested/learn how to use the excelcom.au3 yet. And don't know what result to expect so you really need me to test? I'll try it later.

[igmeou]

Ok. Thank you so much randallc I had finally got it to work. For the au3 script for a fix file.

Now, I need to know how to import all the dated csv files into a Workbook. How to modify the macro so that I can specify the filename to import and the workbook name and the worksheet to be save in certain workbook?

Must it be done inside the excel macro or autoIT script? Or can we write a macro manually into a excel file and excute it?

[randallc]

Hi,

glad you got it working!

Any of your options are OK; you'll probably have to work it out yourself, depending on your setup.

  ActiveWorkbook.SaveAs Filename:="C:\Program Files\AutoIt3\book1.xls", _

        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _

        ReadOnlyRecommended:=False, CreateBackup:=False

As I said, you can change the "saveAs" each time....

If it were up to me, I would probably find it easiest to keep the file names the same inside the Excel Macro, and give it a blank sheet each time by copying a blank sheet to a fixed name with AutoIt, then copying the excel sheet once done to a new name as required.

you could then , say, cycle through all the names in a folder of "dated" 'csv" files, and import them, calling the routine each time, and naming them after?...

[You could pas a nmae to excel via the registry for the save name, but that's getting complex]

all are possibilities.

If you need more help in picking up names of files, again I would probably need more detail of where they were, how many, how named...?..

Best, Randall

[igmeou]

Thanks man.

Just another question, can autoIT pass a variable value to VB macro directly? If not maybe I may try to get autoIT to write the required filenames to a file then using vb to read it in then.

What do you think?

[randallc]

hi,

All are possible;

1. registry read/ write

2. ini read/ write

3. txt file read/ write

4. ExcelCom.au3 to send to a cell!

5. parameters otherwise - ? - not sure in this context; see "cameronsdad" earlier in this thread?

Best, Randall

[randallc]

Hi,

I would like to translate the "import" to AutoIt from VBA.

Start?...

With ActiveSheet.QueryTables.Add(Connection:= _

"TEXT;C:\Program Files\AutoIt3\temp.csv", Destination:=Range( _

"A1"))

Can anyone please help?; The macro works, and can be called...

see full macro earlier in thread..

Excel Import VBAThanks, Randall

EDIT -NEVER MIND -I'm interested for knowledge, but don't need it; found a fast workaround; see UDF thread, Randall

Edited September 17, 2005 by randallc