Importing from Excel with set layout

[eddyb2]

Hi all,

I am importing a lot of data from a csv file but cannot work out the best way to do this because of the format of the csv.

The external program that creates this csv offers up 2 formats.

Basically what I am importing is historic data against stock codes, the views below are how the csv shows in excel with the | being a column divider

1st format offered...

Stockcode|01/01/08|02/01/08|03/01/08|

1234567|356|350|359|

7654321|971|950|800|

2nd format offered

Stockcode|1234567

01/01/08|356

02/01/08|350

03/01/08|359

Stockcode|7654321

01/01/08|971

02/01/08|950

03/01/08|800

What I need to import is 1 record per stockcode per date

So from the examples above I would get 6 records in FileMaker

1 - 1234567 01/01/08 356

2 - 1234567 02/01/08 350

3 - 1234567 03/01/08 359

4 - 7654321 01/01/08 971

and so on...

So 1 record per stockcode per date

Is this even going to be possible with these formats?

I thought about using excel to open the file and run a macro to move everything around to a format that FM would import easily, but due to the amount of raw data I'm bringing in, Excel cannot open the full file and so macros would be useless.

Bearing in mind I am receiving a file with 500 stockcodes with a value for each working day (260 days in total) over the last 12 months

Please tell me that there is some wonderous way to run a script that will make it happen !!

My whole solution goes down the drain if I cannot get this historic data imported and find a way to automatically do this once a year!!

Many thanks in advance

Ed

Edited November 28, 2008 by Guest

[eddyb2]

Can anyone help with this at all?

Many thanks

[comment]

It should be possible by importing the 2nd format into two text fields of a temp table, then looping through the imported records with this logic:

• If the first field contains "Stockcode", put the second field into a $variable;

• Else set the third field to the $variable.

After that you can omit the "Stockcode" records, and import the remaining ones into your real table.

This could all be scripted to happen at once.

[eddyb2]

This sounds perfect!

Would you be so kind as to check my script to make sure this is the best way to do it?

This would come after importing the records...

Enter Browse Mode []

Show All Records

Got to Record/Request/Page [First]

Loop

If [stockHistoricTemp::Field1 = "StockCode"]

Set Variable [$$StockCode; Value:StockHistoricTemp::Field2]

Else

Set Field [stockHistoricTemp::Field3; $$StockCode]

End If

Go to Record/Request/Page [Next; Exit after last]

End Loop

Enter Find Mode []

Set Field [stockHistoricTemp::Field1 = "StockCode"]

Perform Find []

Delete All Records [No dialog]

This will then leave me the records that have a date in Field 1, the figure in Field 2 and the Stock Code in Field 3 ready to import into my Master table?

Many thanks again, life saver!!

[comment]

It seems about right, just a few pointers:

After import, the found set is the records that were imported - so there's no need to show all records or enter Browse mode (I presume the process starts by going to the temp table, showing all records and deleting them, then importing the external file).

A local variable $stockCode should be quite sufficient; there's no need for it to hang around after the script has finished running.

I would omit the "Stockcode" record instead of deleting them (just in case something went wrong and the data needs to be inspected).

Of course, that's just the rough sketch: some error trapping would probably be in order, e.g. the first record must be a "Stockcode" record, and so on.

[eddyb2]

Perfect, thank you very much!

Ed