Hi there!
I have a process that utilizes an Access database to convert a fixed width text file with a header from ver5 to ver6. This is working brilliantly, and the entire process so far takes a little more than a minute. Unfortunately, I have a problem! The Access database is adding extra spaces at the end of the header–1295 extra spaces! We can’t upload the file in such a condition, so I need a way to remove those extra spaces before the file is uploaded by the bot.
I tried to use Find/Replace to replace the 1295 spaces with nothing, but there is a strict count on how many characters can be typed into those Find/Replace fields. I cannot use the amount of digits they are accepting because there are other places in the file where the same amount of spaces exist.
I tried searching on Google to see if there were any solutions online; however, I couldn’t find anything. Also, I thought about using the command prompt to break up the daily file into 2 files–one with just the header and the other one with the records. Then, I would have steps to import the header file into a database to format, then export back out as well as steps to import the records into its own database to format, then export back out. I’m sure it’s doable; however, I feel deep in my soul that there’s got to be a much better way to do this!
I would appreciate any guidance you can provide for this particular situation! Thank you in advance!
1 Like
@BrandonTerry can you please help me understand below statement with an example string?
“I tried to use Find/Replace to replace the 1295 spaces with nothing, but there is a strict count on how many characters can be typed into those Find/Replace fields. I cannot use the amount of digits they are accepting because there are other places in the file where the same amount of spaces exist.”
1 Like
@Kudlappa_Gouder
I had a strong feeling that you were going to focus on that! LOL! Okay, let me see if I can try to write it out better.
I have a process that imports a fixed-width text file into an Access database to convert it from ver5 to ver6. When it exports the ver6 format, the database adds 1295 additional spaces to the header row, which contains the version number, FEIN, filename, and count of records that exist on the file. Below is a screenshot of a daily file in its proper ver6 format.
- This is the version number for the file.
- This is our Corporate FEIN.
- This is a sample filename.
- This is the record count. The 1295 extra spaces are at the end of this field.
- This is the Employee Last Name column.
- This is the Employee First Name column.
The bot opens the ver6 file in regular Notepad to access the Find/Replace window by using the Send Hotkey activity to press CTRL h on the keyboard.
- I tried to have the bot type 1295 spaces into the Find field of the window, but I realized that amount was more than the field allowed.
- I tried to have the bot type 647 spaces (half the amount) into the Find field of the window, but I realized that amount was more than the field allowed as well.
- My final action was to learn the maximum amount of characters that are allowed in this field. I very quickly learned that the Find field only accepts around 22 spaces. This removes this option as a solution because I can’t keep saying Find 22 spaces to remove them on repeat because it will also affect other areas in the fixed-width text file.
I hope this makes it clearer!
The only thing I can think of right now is to use Read Text File, Assign Value to Variable with the Read Text File variable having a substring that pulls out the first 91 characters of the text file, using Send Hotkey activities to delete the extra spacing, and then using Input to Desktop Browser to paste the value that was extracted. It just seems like a lot of work and like something where a better solution exists.
1 Like
@BrandonTerry, you may want to try the following:
So, I’m using the replace() method with a regular expression to match one character, which in this case is a space, repeated by 1295 times.
file_content.replace(/ {1295}/, "")
Hope this help!
1 Like
@dm.ferreyra
You are amazing! You totally rock! Thank you so much!
1 Like
