Importing Excel crosstab style data

[LaRetta]

Well, I searched ... found similar but not this. And I've been trying to figure it out but everything I've tried still takes a LOT of manual work.

I will be presented with 250+ Excel worksheets. These worksheets have a Synch number (which I can use as a match field) and then the column labels are stock codes. The data will be text in 125 of the worksheets and a date in the other 125. I am attempting to automate this process of importing these as individual records. I can achieve importing the labels as fields - but not data. I may have to do this and then split them into records? Oh, I hope I can automatic this a bit more. Worksheets look like:

I want to end up with four fields:

Synch#

StockCode

Comment

Date

Any thoughts on how to attack this problem, other than moving to Tahiti?

ps, I should mention there are anywhere from 20-50 stock codes. And one worksheet is so large it won't even load it all in Excel. I can identify the date fields from the labels so I could script it - import labels also as records and then script replacing through each batch. And maybe I can identify the labels by the dash - they all APPEAR to have one but I am unsure on this.

LaRetta

[Fenton]

I can think of ways to automate post-processing it. Some Excel whiz could probably automate importing them; but it wouldn't be me, not on PC anyway; would be nice though.

First just import all the data, into its respective fields, as is. All fields are text. Then sort by Sync# and export just Sync# summarized by Sync#. That'll give you a FileMaker file of the unique Sync#'s. Create a relationship from Sync# to Sync#.

Then run a loop in your new file. Each record would Go To Related Record [show,"Sync#"] to the original file.

You'd loop through the records in the original file for that Sync#, create records in the new file, and set each field in the new file according to what the column in the original was, and what was in it (text or date). Then go to the next Sync# in the new file and start the process again.

I suppose you could alternatively just Sort the Sync#'s in the original file/table, then create the new records in another table with a Loop. This may be faster. But I kind of like the "control" of having a file with unique Sync#'s (I have issues :-).

It could also possibly be done with Imports; but those are so buggy still (may Import all records each time; no fun at all), that I'd stick with a Loop.

Kind of vague advice I'm afraid. I'm not entirely clear on the data. I assume that the columns are uniform. If not you'd have to line them up manually. 250 is pretty awful. But I think the FileMaker processing would be automated, though it would take a while.

[Ugo DI LUCA]

Hi,

The only way I could find when I had to handle this was to import the file as csv, and then play with a loop through the file, to rebuild the whole records set.

For Matter of clarity, I prefer pipes to commas.

1) Loop through all records building a gCode (global Text) which will end being, for example :

synch#|FGTY-JK|HJUU-VB|....

14595|Approved|Considering|Refused|...

15878||Waiting||

2) From this global, extract the first line and set a gHeader field which will be :

"FGTY-JK|HJUU-VB|...." holding therefore all your Product codes separated by a pipe character.

3) Now, add a gRow and gColumn to your file (global nums)

4) Build a loop which will progress either horizontally (gColumns) and (when reached the number of Product Codes stored in gHeader), vertically (gRow), until you've reached the latest line of gCode and latest Product Code of gHeader)

The script will then create new records in its progression, setting each of your fields :

a- Synch# :

LeftWords(Middle("

[LaRetta]

Oh, I so appreciate both of your inputs. I'm an old 123 User with only intermediate Excel skills. I tested and tried all Excel export options as well as hunted for consolidation-of-worksheet processes to no avail. "I assume that the columns are uniform." Well, no. If no data existed in any Synch line, the Stock Code (column) didn't pull, Fenton. They are in the right sequence though but ...

I've been reviewing plugins which might massage the data a bit for me (pre-FM) but no luck so far; although I have 22,000+ more hits I could check first; but it would probably take longer to research than just doing it. But not tonight. My mind is fuzzy and many ideas are tripping over each other.

Ugo, one thing concerns me about your suggestions, and it's probably because I'm just tired. Combining Stock Codes in one line separated by a pipe; same with the other data. It concerns me because, what if a StockCode cell is empty? They will no longer line up. I could insert a placeholder (maybe ~) I suppose but it doesn't strike me as being easily controlled (I'm with you on those control issues, Fenton). The other reason this would make me nervous - each worksheet will not necessarily match in those Stock Code headers - subsequent worksheets may contain additional Stock Codes in the middle of that sequence. My mind can't even conceive what this would produce or how to keep it organised - column by column. And 50% of the worksheet cells are empty!!

Well, I've dragged one worksheet (the largest one) to my Dev shortcut and created a conversion file. I can only hope this gives me all (or most) of the stock codes as fields. I thought then that data mapping (via import) should match via field name for all remaining worksheets but this doesn't work! Because FM will then only data map to Last Order - because the first line is considered a field instead of field names.

Okay, I'm going to drop each worksheet onto my Dev icon. This will create the field names for me and 'import' all the data. At least it will be consistent to work with. It takes no time to drop one icon onto another. Once converted, importing (although manual) should map on name (StockCode) and run pretty smoothly. And it will be easy to identify comment data from date data so I'll just combine them all. Whew.

Once consolidated, all I'll have to do is split the fields into records. I'll address that tomorrow after some rest; for now, the vacume cleaners need to run in my mind. Thanks again.

LaRetta

[Ugo DI LUCA]

Hi

Combining Stock Codes in one line separated by a pipe; same with the other data. It concerns me because, what if a StockCode cell is empty?

Well, if your data in Excel shows as

Data | Empty | Empty | Data

When converted to comma separated data, it would come as Data;;;Data.

So even if it isn"t readable, you can match the occurrence of ";" or "|" if you converted them to pipes (not necessary), so that what is in between the gColumn occurrence of "|" and the next in the gRow of the gCode correspond to what is in between the gColumn+1 and the next in the gHeader, the +1 because I skipped the Synch# in the gHeader result.

each worksheet will not necessarily match in those Stock Code headers - subsequent worksheets may contain additional Stock Codes in the middle of that sequence.

Don't tell me this.

In this case, for the method outlined, you necessarily would split the spreadsheet in several new spreadsheets.

Note :

Regarding the above formula, if you consider a look to the solution, you may want to change the Synch formula (set by script) to :

a- Synch# :

LeftWords(Middle("

[Ugo DI LUCA]

Attached quick test with csv converted data.

HTH

CrossTabImport.zip

[Ugo DI LUCA]

And here's one with which you may handle multiple Codes as well, displayed in your sreadsheet, as

CODE1|CODE2|CODE3|CODE4

Comment1|Comment2|Comment3|Comment4|

Comment1|Comment2|Comment3|Comment4|

CODE5|CODE6|CODE7|CODE8

Comment5|Comment6|Comment7|Comment8|

Comment5|Comment6|Comment7|Comment8|

This assumes that in this case, the Synch# is empty.

CrossTabImport2.zip

[LaRetta]

Well, I so appreciate all the help both of you have provided me. After some rest (and I didn't go to work today), I saw a way that might be easier. And a test run through the first very large worksheet proves successful.

I created a Key (global text) in my Conversion file. I created a new file 'Products' with: Key, Synch, StockCode, Comment and Date and joined them on Key, setting to 'Allow Created'. I switched to the original conversion form layout FM creates for me - it had all the fields. I deleted the Synch field and ran this script through it. The script took 3 minutes to run on the largest worksheet. Ummm, 3 x 250?? Well, it certainly was easy and I can run this conversion from home while doing other things.

Go To Layout [Converted Form layout ]

Freeze Window

Go To Record/Request/Page [ First ]

Loop

__ Go To Field ['first stock code field in tab order' ]

__ Set Field [ gScriptTmp; Get(ActiveFieldName) ]

__ Loop

____ Set Field [ Worksheet::Key; Synch & " " & Get ( ActiveFieldName )

____ Set Field [ Products::Synch; Worksheet::Synch ]

____ Set Field [ Products::StockCode; Get(ActiveFieldName) ]

____ If [PatternCount(Get(ActiveFieldContents); "/") ]

______ Set Field [ Products::Date; Get(ActiveFieldContents) ]

____ Else

______ Set Field [ Products::Comment; Get(ActiveFieldContents) ]

____ End If

____ Go To Next Field

____ Exit Loop If [ Get(ActiveFieldName) = Converted::gScriptTemp

__ End Loop

__ Go To Record/Request/Page [ Exit after Last, Next ]

End Loop

I think I'm going to hang onto this idea for converting flat-file multiple-same fields to records. It created a lot of blank records (50% cells were blank) but that's okay. I can delete them easily. I originally tried using a Perform Find[] after landing on each field so it would only create records from the found set, but was unable to enter the Specify Find Criteria without a fieldname. Bummer. Oh well, that's a small thing, I think, although that's going on my 'must be solved list' also.

One worksheet down! 249 to go (although I'll probably combine them via import before running this on the rest of them) so I don't have to import the script or set the one relationship again!

Wow. These Get(Active...) functions are quite wonderful!

Update: I just realised I could use an IsEmpty() to test the field and, if empty, skip it so it wouldn't create a blank record. Doh!

LaRetta

[Fenton]

That's very smart, using the field name. I didn't think of that. I didn't realize the worksheets were so inconsistent. I was just going to point out one of pet tips, that Loops run about 10 times faster if you Freeze Window and View as Form (something to do with refreshing the scroll bar I think, which does move in List view).

[Ugo DI LUCA]

Glad you fixed it.

I would be also glad if you could send me a small sample of the data and how you did this at [email protected], as I suspect this is beyond my current knowledge of FM7.

Or is it some developer tools I'm still waiting for...

Thanks

[LaRetta]

I'm attaching an Excel worksheet and fp7 both in case anyone else faces this scenario. The Excel spreadsheet is just a sample of how the data is coming to me - crosstab style. I drop the spreadsheet onto my FM shortcut - which creates the Converted file for me - with 'first row as field names.' The relationship based upon a unique Key does the rest. Only the fields to convert should appear on the form because the script loops through them. And it's important to enter the field name in the script in the Go To Field (immediately after the first loop) whichever field is the FIRST in tab order as this jump-starts the field-by-field process. The rest is generic.

Because the combination of Synch and StockCode is unique, this works. If you may have more than one match on that, you will need to add a third criteria in that Key to make it unique. The point isn't that each record be unique - but each record/field combination. You need to concatenate to create this uniqueness by including the field name. And because each field is unique, setting ANY related field (except the key) will create a new record. 'Allow Creation of Related' needs to be checked in this Key relationship.

Developer tools, Ugo? Nah. This should work fine in either FM7 Pro or Developer7.

LaRetta

Conversion.zip

[Fenton]

One quick thing, for future reference. Before you start one of these crazy "Go To Next Field" Loops, you can go to the 1st field by using:

Commit Record

Go to Next Field

So you don't have to hard-code the 1st field. It's more generic. Same thing though.