Dynamic data import / export

datenbank

simply more productive
Up to 10,000 records can be processed with the freeware version!
Welcome, Guest
Username: Password: Remember me
Welcome to the FlowHeater support forum.

Questions about using FlowHeater are answered here.

TOPIC: Normalise data from Excel to Access

Normalise data from Excel to Access 5 years 1 week ago #2182

  • Aaron Hamilton
  • Aaron Hamilton's Avatar
  • Offline
  • New Member
  • Posts: 2
Hi I have a template with multiple columns of data forecasting for 30 months, each line has 12 text columns used to describe the transaction then there could be up to 30 number entries in the month columns.
Can I use Flowheater to import to Access and combine the 30 Month columns into one, each value would have to have an individual line and be identified by which column it was entered in i.e Column "201112 with value of 100" would need to be split into 2 columns with one showing "201112" and the other "100" also each line would have to have the Descriptive columns repeated.
I hope this makes sense.
The administrator has disabled public write access.

Re:Normalise data from Excel to Access 5 years 1 week ago #2184

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 239
Hi Aaron,

I think it's possible but I'm not sure if I've understand you correctly? Could you please post a short example like this?

Source format
Header
Dataline1
Dataline2

Purpose format
Access Field Names
Field1, Field2, ...

If I have the example format I’ll make a brief example for you.

Note: You can combine 2 or more fields with the String Append Heater.
Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
The administrator has disabled public write access.

Re:Normalise data from Excel to Access 5 years 1 week ago #2187

  • Aaron Hamilton
  • Aaron Hamilton's Avatar
  • Offline
  • New Member
  • Posts: 2
Hi

I have attached an excel book with a tab that shows the template structure and one that showsth Access table structure. I hope it makes sense

Attachment Flowheater_Example.zip not found

Attachments:
The administrator has disabled public write access.

Re:Normalise data from Excel to Access 5 years 1 week ago #2188

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 239
Hi Aaron,

It is possible but a bit tricky.

First you need one GroupOut Heater. This Heater makes copies from each row on the READ Side, in the example eleven for one year. The current row plus eleven copies = 12 months.

Then you need two .NET Script Heater, one for the Accounting_Period and one for the Volume.

Accounting_Period) This Script gets from the AutoID Heater (with GroupOut option) one Input Parameter. With this parameter the script generates the output Date string. This is necessary because there´s no way to get the field header name :-(

Volume) This Script gets also one input parameter form a AutoID Heater (with GroupOut option) and 12 more parameters - the months fields from the certain year. Note: You have to use the right input order.

I've made a brief example how you can do that. Hope this helps?

Attachment Normalise_Excel_Data.zip not found

Attachments:
Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
The administrator has disabled public write access.
Moderators: FlowHeater-Team
Time to create page: 0.101 seconds