Text to Numbers

Question

Level 1

4 points

Text to Numbers

Hi! I have a problem! I'm migrating from an old stock keeping program to a new one. The new one can import XLS files but the old one cant export them.

The old program can only export badly formatted TXT files. Below you can see two articles. I really only need the data from Artnr, Ben‰mn and Inkpris

I would like to have the data from these three put into columns and a row for each product.

I've tried to do this using Automator but without success. Can anyone help me?

Artnr: 000 Artgrp: 99

Ben‰mn: Arbete Kontkod: 2

st

Lagervar Ja Vikt: 0,000

Antal i -5 687,0799 Volym: 0,000

Inaktiv: Nej

Inkpris: 0,00

Anm‰rkni

--------------------------------------------------------------------------

Artnr: 001 Artgrp: 40

Ben‰mn: Arbete ˆvriga Garanti Kontkod: 1

st

Lagervar Ja Vikt: 0,000

Antal i -79,0000 Volym: 0,000

Inaktiv: Nej

Inkpris: 0,00

Anm‰rkni

--------------------------------------------------------------------------

MacBook Pro

Posted on Oct 5, 2019 2:04 PM

Reply

Answer 1

Oct 5, 2019 5:31 PM in response to reflecs

I hope we can help you.

1) If possible, please post the exported file so we can download it and use it as input. If the information is price, then duplicate the file, and remove most of the data leaving only a few entries. Also change information so it is not private.

2) please post a screenshot of how that data looks in the native program

I think this will require a script to form this into a delimited text file. In order to provide such a script, the text til will be most helpful

Reply

Answer 2

SGIII

Level 8

37,864 points

Oct 6, 2019 10:02 AM in response to reflecs

Assuming the text you have posted is representative and the entries are separated by

--------------------------------------------------------------------------

then you can try a script like the one in the post below right after this one.

Copy-paste script into Script Editor (in Applications > Utilities)
Make sure Script Editor is listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.
Click the triangle <run> button in Script Editor and follow the prompt to choose the file containing the exported text similar to what is shown in your example.
Follow the prompt to save the resulting file, giving it an extension so you can import it into your new program, or first into Numbers or Excel.

The result should be in this format.

If you need refinements after trying it on your actual file, just post.

SG

Reply

Answer 3

SGIII

Level 8

37,864 points

Oct 8, 2019 7:50 AM in response to reflecs

reflecs wrote:

It worked but I did not recieve an output file, see screenshot.

Ah yes. I forgot to remove the line

return outTxt

which I added for testing convenience.

Try removing that line.

Your script should now look like this:

Revised script

SG

Reply

Answer 4

SGIII

Level 8

37,864 points

Oct 8, 2019 4:52 AM in response to reflecs

Could you post a longer example of the text output from your old program?

SG

Reply

Answer 5

reflecs Author

Level 1

4 points

Oct 7, 2019 10:59 PM in response to SGIII

I changed my language to English and made sure that both numbers and Script Editor was checked in Accessibility. Still the same error. I then tried to run smaller portions of the file, the complete file contains about 5000 articles. I made smaller portions of about 500 articles instead. After that the script did not give me any errors.

There is still one problem though. The script only repeats itself on the first article row. So if I have a file with 500 articles, the finished CSV file is 1000 rows with the same article (the first one) on every other row with a blank row in between. See attached screenshot.

Reply

Answer 6

reflecs Author

Level 1

4 points

Oct 8, 2019 4:57 AM in response to SGIII

Artnr: 000 Artgrp: 99

Ben‰mn: Arbete Kontkod: 2

st

Lagervar Ja Vikt: 0,000

Antal i -5 687,0799 Volym: 0,000

Inaktiv: Nej

Inkpris: 0,00

Anm‰rkni

--------------------------------------------------------------------------

Artnr: 001 Artgrp: 40

Ben‰mn: Arbete ˆvriga Garanti Kontkod: 1

st

Lagervar Ja Vikt: 0,000

Antal i -79,0000 Volym: 0,000

Inaktiv: Nej

Inkpris: 0,00

Anm‰rkni

--------------------------------------------------------------------------

Artnr: 002 Artgrp:

Ben‰mn: Arbete Gamo Kontkod: 2

st

Lagervar Ja Vikt: 0,000

Antal i -3 228,5000 Volym: 0,000

Inaktiv: Nej

Inkpris: 0,00

Anm‰rkni

--------------------------------------------------------------------------

Artnr: 003 Artgrp: 45

Ben‰mn: Reservdelar Gamo Kontkod: 1

st

Lagervar Ja Vikt: 0,000

Antal i -202,0000 Volym: 0,000

Inaktiv: Nej

Inkpris: 0,00

Anm‰rkni

--------------------------------------------------------------------------

Artnr: 004 Artgrp:

Ben‰mn: Arbete BSA Kontkod: 2

st

Lagervar Ja Vikt: 0,000

Antal i -120,0000 Volym: 0,000

Inaktiv: Nej

Inkpris: 0,00

Anm‰rkni

--------------------------------------------------------------------------

Artnr: 005 Artgrp: 41

Ben‰mn: Reservdelar BSA Kontkod: 1

st

Lagervar Ja Vikt: 0,000

Antal i -47,0000 Volym: 0,000

Inaktiv: Nej

Inkpris: 0,00

Anm‰rkni

--------------------------------------------------------------------------

Artnr: 006 Artgrp:

Ben‰mn: Kontkod: 2

st

Lagervar Ja Vikt: 0,000

Antal i -40,0000 Volym: 0,000

Inaktiv: Nej

Inkpris: 0,00

Anm‰rkni

--------------------------------------------------------------------------

Artnr: 007 Artgrp: 40

Ben‰mn: Kontkod: 1

st

Lagervar Ja Vikt: 0,000

Antal i -6,0000 Volym: 0,000

Inaktiv: Nej

Inkpris: 0,00

Anm‰rkni

--------------------------------------------------------------------------

Artnr: 008 Artgrp:

Ben‰mn: Arbete Tikka studsare Kontkod: 2

st

Lagervar Ja Vikt: 0,000

Antal i -278,0000 Volym: 0,000

Inaktiv: Nej

Inkpris: 0,00

Anm‰rkni

--------------------------------------------------------------------------

Artnr: 009 Artgrp: 22

Ben‰mn: Reservdelar Tikka stud Kontkod: 1

st

Lagervar Ja Vikt: 0,000

Antal i -127,0000 Volym: 0,000

Inaktiv: Nej

Inkpris: 0,00

Anm‰rkni

--------------------------------------------------------------------------

Artnr: 010 Artgrp:

Ben‰mn: Arbete Lincoln Kontkod: 2

st

Lagervar Ja Vikt: 0,000

Antal i -296,5000 Volym: 0,000

Inaktiv: Nej

Inkpris: 0,00

Anm‰rkni

--------------------------------------------------------------------------

Artnr: 011 Artgrp: 40

Ben‰mn: Reservdelar Lincoln Kontkod: 1

st

Lagervar Ja Vikt: 0,000

Antal i -48,0000 Volym: 0,000

Inaktiv: Nej

Inkpris: 0,00

Anm‰rkni

--------------------------------------------------------------------------

Artnr: 012 Artgrp: 0

Ben‰mn: Arbete ˆvrigt Kontkod: 2

st

Lagervar Ja Vikt: 0,000

Antal i -82,0000 Volym: 0,000

Inaktiv: Nej

Inkpris: 0,00

Anm‰rkni

--------------------------------------------------------------------------

Reply

Answer 7

reflecs Author

Level 1

4 points

Oct 6, 2019 12:52 PM in response to SGIII

Hi!

Thank you for your help! But when I try to run the script I get an error. The error is in swedish but maybe it can help?

error "Kan inte ändra vissa data till förväntad typ." number -1700

Reply

Answer 8

SGIII

Level 8

37,864 points

Oct 8, 2019 6:01 AM in response to reflecs

Does this one work better? Post any errors you encounter and can revise as needed.

SG

Script

Reply

Answer 9

reflecs Author

Level 1

4 points

Oct 8, 2019 6:09 AM in response to SGIII

It worked but I did not recieve an output file, see screenshot.

Reply

Answer 10

reflecs Author

Level 1

4 points

Oct 8, 2019 11:49 AM in response to SGIII

THANK YOU! It solved the problem and the article database is now working on my new system! Thank you so much for all your help! You are my hero!

Reply

Answer 11

SGIII

Level 8

37,864 points

Oct 8, 2019 3:30 PM in response to reflecs

Great to hear it worked out! Thanks for the green tick.

SG

Reply

Answer 12

SGIII

Level 8

37,864 points

Oct 6, 2019 10:01 AM in response to SGIII

The script:


set inText to read (choose file with prompt "Choose data file") as «class utf8»

set theDelim to ";" -- change this to whatever delimiter works best in your region

set text item delimiters to "--------------------------------------------------------------------------"

set outTxt to "Artnr" & theDelim & "Ben" & theDelim & "Inkpris" & return
repeat with anItem in inText's text items
	set theArtnr to trim(extractBetween(inText, "Artnr:", "Artgrp:"))
	set theBen to trim(extractBetween(inText, "Ben‰mn:", "Kontkod:"))
	set theInkPris to trim(extractBetween(inText, "InkPris:", "Anm‰rkni"))
	set theLine to text 1 thru -2 of (theArtnr & theDelim & theBen & theDelim & theInkPris)
	set outTxt to outTxt & theLine
end repeat
set outFile to choose file name with prompt "Save the csv file as..."
set f to open for access outFile with write permission
set eof f to 0
write outTxt to f as «class utf8»
close access f

to extractBetween(aString, startMarker, endMarker)
	set text item delimiters to {startMarker, endMarker}
	return aString's text item 2
end extractBetween

to trim(txt)
	--trim spaces and tabs around text
	repeat with i from 1 to (count txt) - 1
		if (txt begins with space) or (txt begins with tab) then
			set txt to text 2 thru -1 of txt
		else
			exit repeat
		end if
	end repeat
	repeat with i from 1 to (count txt) - 1
		if (txt ends with space or txt ends with tab) then
			set txt to text 1 thru -2 of txt
		else
			exit repeat
		end if
	end repeat
	if (txt is space) then set txt to ""
	return txt
end trim

Reply

Answer 13

SGIII

Level 8

37,864 points

Oct 7, 2019 6:06 PM in response to reflecs

Did you make sure both Numbers and Script Editor are listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility?

If you are still have trouble try going to System Preferences > Language & Region and temporarily changing your region to United States, then running the script.

It works well here on a file into to which I copy-pasted your sample text above and then saved.

SG

Reply

Text to Numbers

Similar questions