Forum

CSV Import - turn s...
 
Notifications
Clear all

[Solved] CSV Import - turn section dividers into field

3 Posts
2 Users
0 Reactions
302 Views
(@quercus47)
Posts: 8
Active Member
Topic starter
 

I'm trying to import data from csv files. Each file contains multiple data sets with a single line dividing different regions. I want to take the regions name and turn it into a data field.

RAW DATA:

1/1/2025 - 1/31/2025

Item,Price Raw,Price Sold,Quantity,Cost,Revenue,Margin,Target,Variance,Condition
Michigan
Apples,1.22,2.12,927,1130.94,1965.24,834.3,1200,765.24,
Banans,0.89,1.98,528,469.92,1045.44,575.52,1258,-212.56,
Pears,1.1,2.08,755,830.5,1570.4,739.9,1202,368.4,
Ohio
Apples,1.22,2.58,583,711.26,1504.14,792.88,1200,304.14,
Banans,0.89,2.01,478,425.42,960.78,535.36,1258,-297.22,
Pears,1.1,2.12,622,684.2,1318.64,634.44,1202,116.64,

 

DESIRED RESULT:

Date,Region,Item,Price Raw,Price Sold,Quantity,Cost,Revenue,Margin,Target,Variance,Condition
1/31/2025,Michigan,Apples,1.22,2.12,927,1130.94,1965.24,834.3,1200,765.24,
1/31/2025,Michigan,Banans,0.89,1.98,528,469.92,1045.44,575.52,1258,-212.56,
1/31/2025,Michigan,Pears,1.1,2.08,755,830.5,1570.4,739.9,1202,368.4,
1/31/2025,Ohio,Apples,1.22,2.58,583,711.26,1504.14,792.88,1200,304.14,
1/31/2025,Ohio,Banans,0.89,2.01,478,425.42,960.78,535.36,1258,-297.22,
1/31/2025,Ohio,Pears,1.1,2.12,622,684.2,1318.64,634.44,1202,116.64,

 

I know how to deal with the date, it's getting the Michigan and Ohio translated into a field in each record that I'm not sure about.

Help is appreciated. Thanks.

-Q

 
Posted : 04/03/2025 11:04 am
Riny van Eekelen
(@riny)
Posts: 1201
Member Moderator
 

When you have split the data, the second column [Price Raw] contains null for all rows where the region name is in the first column [Item]. So, add a custom (or conditional) column, call it Region, with the following formula/logic:

if [Price Raw] = null then [Item] else null

Now, fill down the Region column and filter out rows with null in [Price Raw].

 

 

 
Posted : 04/03/2025 3:47 pm
(@quercus47)
Posts: 8
Active Member
Topic starter
 

Thanks!

 
Posted : 05/03/2025 11:46 pm
Share: