Merging Multiple Excel Tabs into a database

[KeithW]

Hi All,

I was wondering if there is a way to automatically import all tabs of an excel file into a database. I have a client that submitted a bunch of Excel Files, each have 15 or 20 tabs in them. They were generated by a tool that parcels out 65,000 lines to a tab and due to the size of the dataset just generates how many ever tabs it takes to takes to chunk up the data. If I do a Connection String I can specify a tab, one at a time... the regular Excel Import does not seem to like multi-tabbed files which would require me to break up the master file(s) into a file-per-tab.

All tabs are formatted exactly alike, just continuing data.

Regards,
Keith

[KeithW]

Re: Merging Multiple Excel Tabs into a database

OK, so I figured out how to get ALPHADAO to do what I wanted, but the Genie ordered that tabs incorrectly, actually sorted on ASCII sequence, so I saved the function as XBasic, now to fix it for the correct sequence and I *should* be good to go, I hope....

[Stan Mathews]

Re: Merging Multiple Excel Tabs into a database

Probably easier to use ole to break the original spreadsheet into one tab per spreadsheet and then import all of them.

'sample usage - ex_splitter("c:\data\my_big_file.xls")
'creates a csv file of each tab of docName in c:\temp
'filename will be 'file tabname.csv
FUNCTION ex_splitter AS L (docName AS C)
	ex_splitter= .F.
	Dim xlApp as p
	Dim myCell as c
	'concatenate spreadsheet name
	Dim sht_name as c
	sht_name = "Master"
	
	xlApp = ole.getobject("","Excel.Application")
	xlApp.Workbooks.Open(docname)
	'debug(1)
	'xlApp.Visible = .T. 'comment this line out if you don't want to see Excel
	IF xlApp.ActiveWorkbook.ReadOnly
		ui_msg_box("Alert","Halting because file is read-only.",UI_OK)
		xlapp.DisplayAlerts = .F.
		xlApp.Workbooks.Close()
		xlapp.DisplayAlerts = .T.
		xlApp.Quit()
		delete xlApp
		exit function
	END IF
	shts = xlApp.Worksheets.Count
	for qx = 1 to shts
		xlApp.Worksheets(qx).Select()
		chk = xlApp.ActiveSheet.Name
		lastrow = xlApp.Activecell.SpecialCells("11").Row
		lastcol = xlApp.Activecell.SpecialCells("11").Column
		lastcell = xlindx_to_txt(lastcol)+lastrow
		xlApp.Range("A1:"+lastcell).Select()
		xlApp.Application.DisplayAlerts = .F.
		xlApp.ActiveWorkbook.SaveAs("c:\temp\file "+chk+".csv",6,.F.)
		xlApp.Workbooks.Close()
		xlApp.Application.DisplayAlerts = .T.
		xlApp.Workbooks.Open(docname)
	next qx
	xlApp.Quit()
	Delete xlApp
	ex_splitter = .T.
END FUNCTION
FUNCTION xlindx_to_txt AS C (colnum AS N )
  If Colnum > 26 Then
    xlindx_to_txt = Chr(Int((Colnum - 1) / 26) + 64) + Chr((mod((Colnum - 1),26) ) + 65)
  Else
    xlindx_to_txt = Chr(Colnum + 64)
  End If
END FUNCTION

[Tom Henkel]

Re: Merging Multiple Excel Tabs into a database

There is a similar request posted here:

http://msgboard.alphasoftware.com/al...mns-to-Records .

Stan responded and so did I. You could modify the code I gave to work its way across the workbook, sheet by sheet, and then populate the table from the variable array.

If you make the array large enough to capture the # of columns and rows in a sheet, loop through the code and then add the records to the table from the array, it should work.

Just remember to clear the array after you have moved the data into the table.

Tom

[KeithW]

Re: Merging Multiple Excel Tabs into a database

Stan & Tom,

THANX for your solutions... I will look at shortly. I was able to get out of this jam with A5's ALPHADAO Import, but not after several attempts to learn some of its finer points I had never had to use before.

It was bad enough to have to go thru the spreadsheets tabs and fix the formatting to be sure it all converted the same way, but then I got a few bruises from DAO, too. I am looking to come up with a method/tool for doing this in a more generalized method as it seems to be coming up over and over again... this particular project brought to a head due to several files with as many as 20 tabs each. My finger tips are numb at the moment, so I figured it was definitely time to put a tool in place going forward.

Again, many thanx for the thoughts and recommendations !!!

Regards,
Keith

[Al Buchholz]

Re: Merging Multiple Excel Tabs into a database

Too bad they don't send you a single csv file instead...

[KeithW]

Re: Merging Multiple Excel Tabs into a database

Agreed, sometimes they think they are being helpful... sometimes it is just the way their systems output the data automatically.
I never know what is come at me, however it is presented, I have to make it work if at all possible, sometimes I just have to go
back to them and have them get me an IT contact so I can find out what other options exist.

Keith

[Stan Mathews]

Re: Merging Multiple Excel Tabs into a database

Good thought, Al. Should be easy to concatenate the csv files into one using SAVE_TO_FILE(). More tomorrow.

[Al Buchholz]

Re: Merging Multiple Excel Tabs into a database

Good thought, Al. Should be easy to concatenate the csv files into one using SAVE_TO_FILE(). More tomorrow.

I originally was referring to the source file to get CSV instead of Excel but Stan you're right converting the multi-tabbed Excel to a single CSV might be easier.

[Stan Mathews]

Re: Merging Multiple Excel Tabs into a database

Made the modification.

'sample usage - ex_splitter("c:\data\my_big_file.xls")
'creates a csv file of each tab of docName in c:\temp
'filename will be 'file tabname.csv
FUNCTION ex_splitter AS L (docName AS C)
	ex_splitter= .F.
	Dim xlApp as p
	Dim myCell as c
	Dim to_combine as C
	to_combine = ""
	'concatenate spreadsheet name
	Dim sht_name as c
	sht_name = "Master"
	
	xlApp = ole.getobject("","Excel.Application")
	xlApp.Workbooks.Open(docname)
	'debug(1)
	'xlApp.Visible = .T. 'comment this line out if you don't want to see Excel
	IF xlApp.ActiveWorkbook.ReadOnly
		ui_msg_box("Alert","Halting because file is read-only.",UI_OK)
		xlapp.DisplayAlerts = .F.
		xlApp.Workbooks.Close()
		xlapp.DisplayAlerts = .T.
		xlApp.Quit()
		delete xlApp
		exit function
	END IF
	shts = xlApp.Worksheets.Count
	for qx = 1 to shts
		xlApp.Worksheets(qx).Select()
		chk = xlApp.ActiveSheet.Name
		lastrow = xlApp.Activecell.SpecialCells("11").Row
		lastcol = xlApp.Activecell.SpecialCells("11").Column
		lastcell = xlindx_to_txt(lastcol)+lastrow
		xlApp.Range("A1:"+lastcell).Select()
		xlApp.Application.DisplayAlerts = .F.
		fil_name = "c:\temp\file "+chk+".csv"
		xlApp.ActiveWorkbook.SaveAs(fil_name,6,.F.)
		xlApp.Workbooks.Close()
		xlApp.Application.DisplayAlerts = .T.
		to_combine = to_combine +fil_name+crlf()
		xlApp.Workbooks.Open(docname)
	next qx
	xlApp.Quit()
	Delete xlApp
	combined_count = line_count(to_combine)
	combined = file.to_string(word(to_combine,1,crlf()))
	save_to_file(combined,"c:\temp\xlscombined.csv")
	for qz = 2 to combined_count
		next_file = file.to_string(word(to_combine,qz,crlf()))
		save_to_file(next_file,"c:\temp\xlscombined.csv",.T.)
	next qz
	ex_splitter = .T.
END FUNCTION
FUNCTION xlindx_to_txt AS C (colnum AS N )
  If Colnum > 26 Then
    xlindx_to_txt = Chr(Int((Colnum - 1) / 26) + 64) + Chr((mod((Colnum - 1),26) ) + 65)
  Else
    xlindx_to_txt = Chr(Colnum + 64)
  End If
END FUNCTION

[KeithW]

Re: Merging Multiple Excel Tabs into a database

Way Cool, Stan !!
Now all I have to do is put a File Chooser Front-End and it will be really useful for my purposes...

Thanx,
Keith

[Al Buchholz]

Re: Merging Multiple Excel Tabs into a database

Sweet....

Keith let us know if the provider can give you a single CSV.

It might save you a lot of work and future maintenance. Also will leave an impression here on the messageboard that excel transfers are limiting and data transfers are easier with CSV files.

(in spite of Stan's great work...)

[Stan Mathews]

Re: Merging Multiple Excel Tabs into a database

The way it is written you will have the column headers written to the csv for each tab in the original spreadsheet. We can eliminate that with

for qz = 2 to combined_count
		next_file = file.to_string(word(to_combine,qz,crlf()))
		[COLOR="#FF8C00"]next_file = *LINE_REMOVE(next_file,1,1)[/COLOR]
		save_to_file(next_file,"c:\temp\xlscombined.csv",.T.)
	next qz

[KeithW]

Re: Merging Multiple Excel Tabs into a database

Al,

Unfortunately it is NOT a single provider, we doing a specialize form of auditing for many clients all over the country and Canada. EVERYONE and I mean everyone gives us data in differing layouts, formats, file types, you name it... I have to deal with it however it comes at us. I am finally getting tired of doing things over and over and really starting to automate and make specialty tools that simply my life... the whole purpose of using a tool such as A5. I use about half a dozen different tools to accomplish what I need... there is not a single tools out there that can do all of what I need to accomplish. Once the data is in A5 or Excel my life becomes a lot easier, but you would not believe the ways the data arrives in the first place.

Regards,
Keith

Sweet....

Keith let us know if the provider can give you a single CSV.

It might save you a lot of work and future maintenance. Also will leave an impression here on the messageboard that excel transfers are limiting and data transfers are easier with CSV files.

(in spite of Stan's great work...)

[KeithW]

Re: Merging Multiple Excel Tabs into a database

Stan,

You are absolutely correct... I was going to make this kind of modification cause as I read thru your code I knew this would be an issue to side step.
Thanx Again !!!

Regards,
Keith

The way it is written you will have the column headers written to the csv for each tab in the original spreadsheet. We can eliminate that with

for qz = 2 to combined_count
		next_file = file.to_string(word(to_combine,qz,crlf()))
		[COLOR="#FF8C00"]next_file = *LINE_REMOVE(next_file,1,1)[/COLOR]
		save_to_file(next_file,"c:\temp\xlscombined.csv",.T.)
	next qz