Help Creating a Large Excel File

Questions & Suggestions
1

We have to upload new pricing for customers into our ERP, NetSuite. The format of the Excel file need to be:

Customer ID, Customer Name, SKU 1, Price
Customer ID, Customer Name, SKU 2, Price
Customer ID, Customer Name, SKU 2, Price

I have the Excel pricing file and it's approximately 300 lines long. I have an Excel customer file and it contains about 500 unique customers. I need to Paste the first Customer ID and Customer Name into the price file for all 300 rows. Then paste the second Customer ID and Customer Name for all 300 rows. There must be a way to automate this. Any thoughts all you smart people out there?

Thanks in advance

EDIT - The final file is actually a CSV file for the upload so it actually looks like

,,SKU1, Price
,,SKU2, Price

So I guess this could be done is something like BBEdit as well.

2

A few questions for clarification:

  1. Are the price and customer files in CSV format?
  2. Can you upload shortened and anonymized versions of the price and customer files? Five entries of each should be fine, and faking the various entries is OK. We just need to see the formats.
  3. It sounds like you want the output to be about 150,000 rows: 300 price rows for each of the 500 customers. Is that right?
  4. Is this a one-off automation, or do you expect to have to run it regularly?
3

  1. Yes, CSV. Below is the transition between one customer and the next. I copied this out of BBEdit because that might be a better place to do it. You can see the Cust ID is 22 and the Cust Name is Tim. The lower portion of the file is just pricing without the added information.

22,Tim,541995700,6415,Custom,USA,6798.44
22,Tim,547771200,7636,Custom,USA,365.99
22,Tim,54199022BT,20581,Custom,USA,563.88
22,Tim,541155260,2540,Custom,USA,156.36
22,Tim,541155270,2571,Custom,USA,159.37
22,Tim,54115246BT,8000,Custom,USA,36.64
22,Tim,545203990,1216,Custom,USA,162.04
,,541001670,17161,Custom,USA,302.76
,,541001680,21032,Custom,USA,490.89
,,541001690,21033,Custom,USA,612.00
,,541001770,22037,Custom,USA,131.27
,,541001780,22027,Custom,USA,269.46

  1. Yes, roughly 150,000 lines/rows (actual 319 SKU's, 620 customers) Probably need to break it up into files that contain about 10,000 lines in the event the upload fails

  2. This automation would be used at least 3 times per year and depending how it worked, probably 10-20 times.

Hope this helps.

4

Hey Patrick,

Please post discrete examples of each data-sample.

Before...

And what it's supposed to look like after.

That said – you do know you can paste columns in BBEdit – yes?

-Chris

5

Chris beat me to it, but I'm going to second what he said and explain why we need sample inputs and outputs:

  1. Someone's description of an input file is no substitute for an actual input file. We all tend to forget features when describing a file, and those features can make the difference between a successful automation and an unsuccessful one.

  2. Similarly for the output file. I notice, for example, that your answer to my second question has seven fields while your original description of the output had only four.

  3. When you paste data into the forum instead of uploading files, you're forcing the people who are trying to help you to do more work, because they have to take the pasted-in data and create their own files.

  4. The uploaded files, even if they're truncated or have fake data, must include all the salient parts of the files you intend to use the automation to read and write. For CSV files, this means including all the fields and including a header line if that's what the real CSV files will have.

The goal is to get you an answer as quickly as possible. What you're asking for is pretty simple, but the devil is in the details.

1 Like
6

@PLM382

What Drang said...

Be advised that you can post zipped text files to the forum.

OR – you can post a minimal test-case macro with the requisite data in it.

Yes – plus the forum takes markdown and renders it on the web page – and this frequently mangles important bits in example data.

-Chris

7

Attached are three files, two are CSV and one is Excel.

One of the CSV files is labeled input - it's the price list without the customer names.

The Customer Excel file is a shortened version of the customer file. The first two columns of the customer file need to be on every line of the final output file.

The other CSV file labeled "output" shows the desired output for the first two customers in the Customer file.

The project is to copy the first two columns from the Customer file, paste 300 (ish) times in the price files first two columns, paste a new price listing below that, copy the customer info on the next line of the customer file, paste that 300 times, repeat, repeat.

Hope this is clear enough.

Thx

Archive.zip (17.3 KB)

8

Here's a macro that seems to work with the files you uploaded:

ERP.kmmacros (3.5 KB)

20211230-KM ERP
20211230-KM ERP1068Ă—2530 309 KB

It asks for three files:

  1. The input customer file in CSV format. You'll have to export this file to CSV from within Excel before you run the macro. It will have a header line, just like the Excel file does.
  2. The input price file, which is in CSV format with no header line.
  3. The output file for uploading, which will be in CSV format with no header line.

I used Python for the script that does the work, partly because I'm used to it, partly because it has a nice CSV library. There are some caveats:

  • The script is set to run under Python 2, a version of the language that's been deprecated but is still installed on every Mac. It's possible that future versions of macOS will drop Python 2.
  • The script will run under Python 3. If you have that and prefer it, just change the first line to the path to your Python 3 executable.
  • The script may have trouble with non-ASCII characters in either of the input files. Because Python 2 and Python 3 handle non-ASCII characters differently and the samples you provided didn't have any such characters, I decided to avoid the mess of dealing with character encodings. If you need to deal with non-ASCII characters, we can revisit this. (Excel on the Mac isn't great at handling non-ASCII, either.)
  • Because the files you gave me have CRLF (Windows-style) line endings, that's what the macro produces.

Let me know how it works.

9

Update
I searched for python3 on my Mac and the path is "/usr/bin/python3"

I updated the script with a "3"
Re-ran but got no output or error.

First - thank you for putting this together.
It didn't work the first time a ran it. I got an error saying this needs to be updated (I'm running the latest OS on a new MBP). On subsequent attempts, I did not get the error message but the default Output file was not created.

The first line is: #/usr/bin/python
should that end in python2
or should I just try python3?

thanks

10

Ok, this may take a bit of back and forth. Apple has made running Python far more difficult than it should be.

First, it sounds like you're running Monterey. Is that correct?

If so, open Terminal and run each of the following commands. Tell me what the output is.

which python
python --version
/usr/bin/python --version
which python3
python3 --version
/usr/bin/python3 --version

Finally, do you have Xcode installed? What about Xcode Command Line Tools?

As for your questions about the first line of the script:

  • Yes, I did intend it to be #!/usr/bin/python. This has always been the Python 2 executable. No need to stick a 2 on the end, but it does need the exclamation point.
  • We'll figure whether you can use Python 3 by sticking a 3 on the end of it after I get answers to the above output.

Also:

I got an error saying this needs to be updated

What needs to be updated? What exactly was the error message? Was it this?

WARNING: Python 2.7 is not recommended.
This version is included in macOS for compatibility with legacy software.
Future versions of macOS will not include Python 2.7.
Instead, it is recommended that you transition to using 'python3' from within Terminal.

Python 2.7.16 (default, Aug 30 2021, 14:43:11)
[GCC Apple LLVM 12.0.5 (clang-1205.0.19.59.6) [+internal-os, ptrauth-isa=deploy on darwin
Type "help", "copyright", "credits" or "license" for more information.

(If anyone knows how to turn off the syntax highlighting between triple quotes, please teach me.)

11

Which python returned "python"
python returned " WARNING: Python 2.7 is not recommended.

This version is included in macOS for compatibility with legacy software.

Future versions of macOS will not include Python 2.7.

Instead, it is recommended that you transition to using 'python3' from within Terminal.

Python 2.7.18 (default, Nov 13 2021, 06:17:34)

[GCC Apple LLVM 13.0.0 (clang-1300.0.29.10) [+internal-os, ptrauth-isa=deployme on darwin

Type "help", "copyright", "credits" or "license" for more information.

"

And it looks like it left me in python

/usr/bin/python returned the exact same as above

python3 returned "xcrun: error: invalid active developer path (/Library/Developer/CommandLineTools), missing xcrun at: /Library/Developer/CommandLineTools/usr/bin/xcrun
patrickmadigan@MacBook-Pro ~ % "

/usr/bin/python3 returned the exact as just bove

Not sure about Xcode. Basic installation but have room.

The error message won't come back up but it looked like it was from KM and said something like "This app needs to be updated. You should contact the developer..." with learn more and cancel buttons.
The learn more was a post about moving from python 2 to python3.

Error message came back:

image

12

@peternlewis?

See the post above.

I've seen this at least once in passing (not on my personal system), but I don't recall seeing the explanation...

"Keyboard Maestro Engine"
needs to be updated

13

This makes no sense to me. I don't know what's going on, but which always returns the full path to an executable. That's the point of it.

And it looks like it left me in python

Yeah, that's my fault for not knowing how to avoid syntax highlighting in the forum. When I wrote

python --version

I meant it to look like

python --version

so you'd actually type the --version part and wouldn't be left with the Python interpreter running.

Anyway, a few things are clear:

  1. You have Python 2 installed at /usr/bin/python, as expected.
  2. You don't have Python 3 installed.
  3. You don't have Xcode or its Command Line Tools installed. You'd know it if you did.

What isn't clear is why you're getting the Keyboard Maestro error you showed the screenshot of. I don't get that error, and I've never had a problem running Python 2 scripts through Keyboard Maestro, even after the deprecation. Perhaps @peternlewis will shed some light on it now that @ccstone has alerted him.

I'm going to see if sleeping on it brings any revelations.

14

An old 32 bit version of Keyboard Maestro ?

15

I believe this is simply the system deciding that Keyboard Maestro Engine is using the system installed version of python which will stop existing, and then ever so helpfully saying that Keyboard Maestro Engine needs to be updated, rather than the more accurate “your macro is using the system python and will fail in future versions of macOS when it is removed”, which is still largely useless but at least not entirely inaccurate.

2 Likes
16

I couldn't get the Python thing to work but was able to do it entirely within KM. I created a macro to do the operation once and then used a "master" macro to call this one 50 times and did the replacement in batches. The combination of BBedit and KM made this possible. I also used the Named Clipboard to store the price file so I could repeatedly paste it to the bottom of the file. Posted the macro in case it helps someone else. One of the big wins was there is a command key option to trigger Replace All in BBedit.

Thanks, all of you, for the help - great community.

CleanShot 2021-12-31 at 14.32.29@2x
CleanShot 2021-12-31 at 14.32.29@2x892Ă—3604 488 KB

ERP2.kmmacros (9.6 KB)

1 Like