Simplify csv import export workflow

%3CLINGO-SUB%20id%3D%22lingo-sub-2212446%22%20slang%3D%22en-US%22%3ESimplify%20csv%20import%20export%20workflow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2212446%22%20slang%3D%22en-US%22%3E%3CP%3EI'd%20like%20to%20simplify%20the%20workflow%20to%20importing%20data%20from%20a%20service%20I%20subscribe%20to%20into%20my%20database.%20I've%20been%20employing%20the%20following%20steps.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20step%20is%20exporting%20data%20from%20my%20source.%20When%20I%20Export%20this%20data%20it%20creates%20an%20excel%20csv%20file%20which%20downloads%20onto%20my%20Mac.%20Let's%20call%20this%20sheet%20the%20%22export%20file%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20second%20step%20is%20to%20upload%20it%20to%20my%20database%20using%20a%20different%20CSV%20file.%20We'll%20call%20this%20the%20%22import%20file%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20the%20intent%20to%20eliminate%20repetition%2C%20I%20decided%20to%20create%20a%20standard%20non-csv%20excel%20file%20that%20has%20contains%202%20tabs.%20(Let's%20call%20it%20the%20%22non-csv%20file%22)%20%26nbsp%3BThe%20first%20tab%20has%20a%20copy%20of%20the%20export%20column%20arrangement.%20The%20second%20tab%20has%20a%20copy%20of%20the%20import%20column%20arrangement.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20only%20import%20a%20fraction%20of%20the%20data%20from%20the%20export%20file%20(name%2C%20address%2C%20phone%2C%20etc).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20automate%20the%20process%20and%20have%20data%20populate%20onto%20the%20correct%20columns%20of%20the%20import%20tab%2C%20I%20utilize%20a%20formula.%20For%20example%2C%20%26nbsp%3Bif%20I'm%20going%20to%20import%20the%20First%20Name%2C%20Last%20Name%20and%20Address%20and%20all%20three%20are%20on%20ABC%20columns%20respectively.%20I%20would%20place%20the%20following%20equations%20on%20the%20corresponding%20columns%20of%20the%20import%20tab.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EImport%20Row%202%20Name%20Column%20%3D%20export!A2%26nbsp%3B%3C%2FP%3E%3CP%3EImport%20Row%202%20Last%20Name%20Column%20%3D%20export!B2%3C%2FP%3E%3CP%3EImport%20Row%202%20Address%20Column%20%3D%20export!B2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20the%20data%20is%20in%20place%2C%20I%20copy%20the%20rows%20that%20has%20the%20data%20and%20paste%20it%20special%20on%20the%20import%20CSV%20file%20and%20upload%20to%20my%20database.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20works%20on%20the%20first%20import.%20However%2C%20When%20I%20clear%20the%20prior%20data%20and%20paste%20the%20new%20batch%20of%20data%2C%20It%20appears%20that%20the%20reference%20equations%20aren't%20doing%20their%20job%20because%20the%20column%20and%20row%20data%20has%20changed%20on%20the%20equation%20to%20generic%20column%20and%20row%20info.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I'm%20doing%20it%20wrong%2C%20Is%20there%20another%20way%20to%20accomplish%20the%20same%20thing%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20I%20was%20able%20to%20make%20sense%20in%20my%20description%20of%20the%20problem.%20%26nbsp%3BOtherwise.%20feel%20free%20to%20reach%20out.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2212446%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2219791%22%20slang%3D%22en-US%22%3ERe%3A%20Simplify%20csv%20import%20export%20workflow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2219791%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3EFor%20this%20kind%20of%20tasks%20it%20is%20better%20to%20use%20power%20query.%20You%20set%20up%20the%20system%20once%20and%20then%20the%20whole%20procedure%20becomes%20automatic.%20You%20can%20also%20set%20the%20query%20to%20get%20the%20last%20file%20in%20a%20directory%20so%20that%20whenever%20you%20copy%20the%20new%20.csv%20file%2C%20the%20query%20will%20automatically%20takes%20it%20and%20updates%20all%20the%20data.%20The%20only%20thing%20you%20need%20to%20do%20would%20be%20usins%20save%20as%20dialog.%3C%2FLINGO-BODY%3E
New Contributor

I'd like to simplify the workflow to importing data from a service I subscribe to into my database. I've been employing the following steps. 

 

The first step is exporting data from my source. When I Export this data it creates an excel csv file which downloads onto my Mac. Let's call this sheet the "export file". 

 

The second step is to upload it to my database using a different CSV file. We'll call this the "import file". 

 

With the intent to eliminate repetition, I decided to create a standard non-csv excel file that has contains 2 tabs. (Let's call it the "non-csv file")  The first tab has a copy of the export column arrangement. The second tab has a copy of the import column arrangement. 

 

I only import a fraction of the data from the export file (name, address, phone, etc). 

 

To automate the process and have data populate onto the correct columns of the import tab, I utilize a formula. For example,  if I'm going to import the First Name, Last Name and Address and all three are on ABC columns respectively. I would place the following equations on the corresponding columns of the import tab. 

 

Import Row 2 Name Column = export!A2 

Import Row 2 Last Name Column = export!B2

Import Row 2 Address Column = export!B2

 

Once the data is in place, I copy the rows that has the data and paste it special on the import CSV file and upload to my database. 

 

This works on the first import. However, When I clear the prior data and paste the new batch of data, It appears that the reference equations aren't doing their job because the column and row data has changed on the equation to generic column and row info. 

 

If I'm doing it wrong, Is there another way to accomplish the same thing?

 

I hope I was able to make sense in my description of the problem.  Otherwise. feel free to reach out. 

7 Replies
Hello,
For this kind of tasks it is better to use power query. You set up the system once and then the whole procedure becomes automatic. You can also set the query to get the last file in a directory so that whenever you copy the new .csv file, the query will automatically takes it and updates all the data. The only thing you need to do would be usins save as dialog.

Thanks @erol sinan zorlu

Can you suggest a source that provides instructions on how to achieve that?

For a basic study you can get M for Data Monkey book. For advanced applications it is up to you.

@A_Castro1625 Since you indicated that you work on a Mac, you may forget about using Power Query.

@A_Castro1625 

Afraid that won't work on Excel for Mac, Power Query has quite limited functionality for it.

Thanks. Glad you told me that before I invested the time into it.
Thanks