Excel Save Each Row As Text File

[edauthier]

Anyone have a script that they could share that perfoms this?

[edauthier]

Maybe I should have been more clear.
I am working with MS 10 - DB Connect command. I am struggling with the array dimensions for db connect.

Marcus's blog instructs..
rsSheet1_1 is the table array, etc.

The first datacell contents can be found in rsSheet1_1_1 and I can increase this incrementally across the row until nF, but it seems somewhat inefficient. Probably because I don't have the concept completely.


The variables for nF and nR work perfectly to return the number of rows and columns (less the row header).

Just looking to see if anyone is using DB Connect with Excel and having success.

Ed


PS- nsSheet references? Not exactly clear.

Code: Select all

Let>connStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%SCRIPT_DIR%\example1.xls;Extended Properties=Excel 8.0;

DBConnect>connStr,dbH
              Let>SQL=select * from [Sheet1$]
    DBQuery>dbH,SQL,rsSheet1,nR,nF
    DBClose>dbH

message>nF  is the number of columns %crlf%  nR is the number of rows

[JRL]

Ed,
Odd that you would make this addition to your thread while I was typing the following. This is a method I've used in the past for collecting info from excel. I've not yet used any of the new database functions. This method puts the used portion of sheet 1 to the clipboard then separates first by CRLF then by TAB if cell info is required. Your original post says you want to write individual rows so the separate by TAB is excluded in this sample.

Perhaps there is something in here that will be helpful. Otherwise this method will work, though probably not nearly as clean as the new functions.

Later,
Dick

Code: Select all

//Location to write files,  must be in the form drive:\folder1\folder2\etc
Let>WriteFileLocation=%temp_dir%WriteExcelTest\nexttestfolder\lasttestfolder
IfDirExists>WriteFileLocation,Continue
Separate>WriteFileLocation,\,Folder
Let>Folders=1
Let>path=%Folder_1%
Repeat>Folders
  Add>Folders,1
  Let>value=Folder_%Folders%
  Concat>Path,\%value%
  CreateDir>Path
Until>Folders,%folder_count%

Label>Continue
IfWindowOpen>Excel*
  MDL>Excel is open.%CRLF%Please close Excel and restart the macro.
  Goto>EOF
EndIf
Input>file,Excel file to be processed
VBSTART
VBEND
VBEval>Timer,StartTime
BlockInput>1
IfFileExists>%file%,,EOF
ExecuteFile>%file%
WaitWindowOpen>Excel*
SetFocus>Excel*
  //Ctrl+G calls Excel Goto window.  A1 is the first cell.
  Press Ctrl
  Send>g
  Release Ctrl
  WaitWindowOpen>Go To*
  SetFocus>Go To*
  Send>A1
  Press Enter
  Wait>0.5

  //Ctrl+Shift+End highlights from current location to the last cell with data
  Press Ctrl
  Press Shift
  Press End
  Release Shift
  Release Ctrl
  Wait>0.2

  //Ctrl+c puts highlighted cell contents in the clipboard
  Press Ctrl
  Send>c
  Release Ctrl

  //Wait for the clipboard to be ready
  WaitClipBoard
  Wait>0.2
  GetClipboard>Data
  CloseWindow>Excel*
  Let>WW_TIMEOUT=5
  WaitWindowClosed>Excel*
  If>WW_RESULT=FALSE
    Send>n
	Wait>0.5
  EndIf
  Separate>Data,%CRLF%,Row
Let>kk=0
Repeat>kk
  Add>kk,1
  Let>value=Row_%kk%
  WriteLn>%WriteFileLocation%\Row_%kk%.txt,wres,%value%
  Message>Processing Row %kk%
Until>kk,%Row_count%

Label>EOF
BlockInput>0
VBEval>Timer,EndTime
Let>TotalTime=%EndTime%-%StartTime%
MDL>TotalTime = %TotalTime%

[Marcus Tettmar]

I think this does what you want:

Code: Select all

Let>connStr=Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=%SCRIPT_DIR%\example.xls;

Let>connStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%SCRIPT_DIR%\example.xls;Extended Properties=Excel 8.0;
DBConnect>connStr,dbH

Let>SQL=select * from [Sheet1$]
DBQuery>dbH,SQL,rsSheet1,nR,nF

//save EACH ROW to a text file

//walk through each row
Let>row=0
Repeat>row
  Let>row=row+1

  //for each row, walk through each cell (field)
  Let>col=0
  Let>row_text=
  Repeat>col
    Let>col=col+1

    //get this cell
    Let>cell=rsSheet1_%row%_%col%

    //append it to our row_text string
    Let>row_text=%row_text%%TAB%%cell%

  Until>col=nF

  //write the line of text to a new file
  WriteLn>d:\rowfile_%row%.txt,r,row_text
Until>row=nR

DBClose>dbH

This works on the example.xls file that is shipped with Macro Scheduler samples. It works through each row and saves each row to a text file. I ended up with 151 text files called rowfile_1.txt to rowfile_151.txt. Each one containing each cell in the row separated by TABs.

It took only a few seconds - any delay is in the writing to the text file.

Not sure if it's exactly what you want, but hope it helps.

[edauthier]

Thanks for the great responses. I have one other issue to deal with. I only need data from certain 'static' columns. Still attempting to create the lightest and most expedient script.


The process:
- Capture all spreadsheet rows
- Only certain columns
- Build a somewhat structured text file
- Name of Text file derived from 3rd column cell data

The real script has hundreds of columns to deal with and the number of rows varies, but typically more than 600.

So here is a similar example script:

Code: Select all

'WriteLine uses GUITARIST for output text file name.
WriteLn>c:\1\%rsSheet1_1_3%.txt,,%crlf%[SINGER]: %rsSheet1_1_1%%crlf%%crlf%[GUITARIST]: %rsSheet1_1_3%%crlf%%crlf%[BASSIST]: %rsSheet1_1_5%%crlf%%crlf%[DRUMMER]: %rsSheet1_1_6%%crlf%%crlf%

Example of the source: (columns 2 and 4 are ignored do not need data)

Code: Select all

Spreadsheet Source

___1______2_____ 3______4____5_____6__>>
||Bono|     NA  |  Edge  |   NA  | Adam  | Larry|
|_____|______|______ |_____|______ |_____|
||John |    NA   | George|  NA   | Paul  |  Ringo|
|_____|______|_______|_____|_____|______|

Example output:

Code: Select all

Textfile Results
____________________

Row 1

Filename - Edge.txt


[Singer]: Bono

[Guitarist]: Edge

[Bassist]: Adam

[Drummer]: Larry


____________________

Row 2 

Filename - George.txt


[Singer]: John

[Guitarist]: George

[Bassist]: Paul

[Drummer]: Ringo

[edauthier]

Finishing up the script and I have one lingering issue.
Variable - rsSheet1_1_1 It starts at Cell A2 and I need to start at A1.
It's like I need rsSheet1_1_0

The window message in this script returns Cell A2. Need Cell A1.

Code: Select all

Let>connStr=Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=%SCRIPT_DIR%\example.xls;

Let>connStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%SCRIPT_DIR%\example.xls;Extended Properties=Excel 8.0;
DBConnect>connStr,dbH

Let>SQL=SELECT * FROM [Sheet1$]
DBQuery>dbH,SQL,rsSheet1,nR,nF

Message>%rsSheet1_1_1%

DBClose>dbH

[Marcus Tettmar]

I did a bit of research and find that by default ADO is treating the first row as column names. From: http://support.microsoft.com/kb/257819

Column headings: By default, it is assumed that the first row of your Excel data source contains columns headings that can be used as field names. If this is not the case, you must turn this setting off, or your first row of data "disappears" to be used as field names. This is done by adding the optional HDR= setting to the Extended Properties of the connection string. The default, which does not need to be specified, is HDR=Yes. If you do not have column headings, you need to specify HDR=No; the provider names your fields F1, F2, etc.

So, the solution is to change your connection string to:

Let>connStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%SCRIPT_DIR%\example.xls;Extended Properties="Excel 8.0;HDR=No;"

[edauthier]

Thanks alot.

Here's an example final for this portion of the script. Runs very nicely with alot less code than in the past. With this base you can then apply RegEx , etc.

Code: Select all

Let>connStr=Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=%SCRIPT_DIR%\band.xls;

Let>connStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%SCRIPT_DIR%\band.xls;Extended Properties=Excel 8.0;HDR=No;"
DBConnect>connStr,dbH

Let>SQL=select * from [Sheet1$]
DBQuery>dbH,SQL,rsSheet1,nR,nF

'Increment Thru Rows
    Let>row=1
Repeat>row
    Let>row=row+1

'Static Defined Columns
  Let>cell1=rsSheet1_%row%_1
  Let>cell3=rsSheet1_%row%_3
  Let>cell5=rsSheet1_%row%_5
  Let>cell6=rsSheet1_%row%_6

'Tie Columns Together As String
    Let>row_text=[SINGER]: %cell1%%CRLF%[GUITARIST]: %cell3%%CRLF%[BASSIST]: %cell5%%CRLF%[DRUMMER]: %cell6%%CRLF%

'Writes File and Names Column 2 From Each Row
WriteLn>c:\1\test\%cell3%.txt,r,row_text
        Until>row=nR

DBClose>dbH