After seeing one of my colleagues ‘battle’ with Excel modify to sheet to prepare it in a CSV format for the sticker printer, I decided to help with a little Python script, which afterwords has processed thousands of lines for them. The sticker printer only reads from one column and in CSV format whereas the data from Sage are in the same row and not next to each other. The scripts does not account for the other columns as I get the file with only the 2 required columns. Here I will be given a breakdown of the script and what it does.
- The Source and Processed File.
- The Loop (While Source File is Open).
- Initialize Variable for Validation.
- The Validation for Bin Location Lengths.
- Writing to the New File.
- Closing the Files.
Step 1: The Source and Processed File
This is where the file locations are specified for the source file and the new processed file. I’ve grouped the files locations together that you just have to look in one place for the file locations.
fileToProcess = 'C:\location\of\file\AA.csv' fileProcessed = open('C:\location\of\file\AAProcessed.csv', 'w')
Step 2: The Loop (While Source File is Open)
Here we are opening the file source file, specified in string from step 1, and we will process each line until end of the file is reached.
The ‘with open(fileToProcess) as f:’ opens the file in a loop.
The ‘for line in f’ iterates through each line of the file.
The ‘s2p = line.split(‘,’)’ splits each ‘cell’ into an array for use in the validation and writing to the new file.
# Open file in a loop with open(fileToProcess) as f: # While the file is open process all the lines (Until End of File) for line in f: # Split the row into an array s2p = line.split(',')
Step 3: Initialize Variable for Validation
Now we are going to initialize the variable for the validation so that it will be available outside the ‘if-elif-else’ scope.
s1a = ""
Step 4: The Validation for Bin Location Lengths
Now we are entering the validation. There are two lengths for the bin locations, 7 and 8 characters. When not considering that and just printing the rest of the string, it prints a new line which comes from the source file, which I did not want to use. The validation checks the length of the bin location and adds a ‘-‘ between the different section of the bin location for ease of reading. This is done to ensure there are no blank stickers that will be printed or missing characters for the bin locations. I’ve added the else to print ‘ERROR’ to see if there are any changes in the length of the bin locations to either consider adding a new length are a missing bin location from the source file.
# Processing the bin location number for the different lengths # Not to add additional lines that will print blank sticker. if len(s2p) == 8 : # For 3 Digit of Position s1a = s2p[:2]+"-"+s2p[2:4]+"-"+s2p[4:7] elif len(s2p) == 9 : # For 4 Digit of Position s1a = s2p[:2]+"-"+s2p[2:4]+"-"+s2p[4:8] else: # Print error if the length of the bin location is not 8 or 9 characters # long. Just as precaution if it happens or error in CSV file. print("ERRROR")
Step 5: Writing to the New File
Here we will be writing the processed data to the new file, each in its own line.
# Write the new rows to the files fileProcessed.write(s1a+'\n') fileProcessed.write(s2p+'\n')
Step 6: Closing the Files
Here we close the files that we opened and are not block any more.
# Close the files f.close() fileProcessed.close()