Mar 15 2021 06:21 PM
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.
Mar 18 2021 05:21 AM
Mar 18 2021 09:07 AM
Thanks @erol sinan zorlu,
Can you suggest a source that provides instructions on how to achieve that?
Mar 18 2021 09:11 AM
Mar 18 2021 09:35 AM
@A_Castro1625 Since you indicated that you work on a Mac, you may forget about using Power Query.
Mar 18 2021 09:35 AM
Afraid that won't work on Excel for Mac, Power Query has quite limited functionality for it.
Mar 21 2021 02:24 PM