Converting symbols when importing data from a .txt file.

[romankonis]

Hello! I am a beginner and I need help of advanced users in the matter of converting characters, namely from period to comma. What's the point? There is a CSV file, it contains data.

Example - 5.00%, 20.00%, 2.456, 22.564 and so on. Below is a screenshot of the file so that you understand the content. When importing data from a file into an existing table, the data is displayed as follows - 500 2000 2456 22564 As you noticed, data has no structure and is just a set of numbers. The screenshot will show how it looks after import, and it will also show how the data after import should be displayed. In short - In the imported file, there is a dot between the numbers, and the data is not displayed correctly during import. For import, the button that has the script is responsible, the contents of the script I also gave you below. Guys, I'm in a quandary, help a newbie)) Of course, you can manually change the data in the imported file, namely, instead of periods, propyate commas, or after import, you can manually write wherever a comma is required in numerical values. But, this option is not suitable as well as using third-party software to replace characters. There is a task to use the capabilities of fmp and the existing script.

 

Script

Set Variable [ $MainID; Value:Sails::MainID ]
Go to Layout [ “PragmatikesTimesTABLE” (RealValue) ]
Show All Records
Set Error Capture [ On ]
Show Omitted Only
Perform Find [ Specified Find Requests: Find Records; Criteria: RealValue::MainID: “$MainID” ]
[ Restore ]
If [ Get ( FoundCount ) ≠ 0 ]
Show Custom Dialog [ Title: "Υπάρχουν εγγραφές "; Message: "Υπάρχουν εγγραφές να τις διαγράψω;"; Default Button: “Ναί -
Διέγραψε”, Commit: “No”; Button 2: “ΟΧΙ - Ακύρωση”, Commit: “No” ]
If [ Get ( LastMessageChoice ) = 2 ]
Exit Script [ ]
End If
Delete All Records
[ No dialog ]
End If
Import Records [ ]
Go to Record/Request/Page
[ First ]
Loop
Set Field [ RealValue::MainID; $MainID ]
Set Field [ RealValue::X_position; Trim (RealValue::X_position) ]
Set Field [ RealValue::Y_camber; Trim (RealValue::Y_camber) ]
Set Field [ RealValue::Y_draft; Trim (RealValue::Y_draft) ]
Set Field [ RealValue::Y_twist; Trim (RealValue::Y_twist) ]
Set Field [ RealValue::Y_entryA; Trim (RealValue::Y_entryA) ]
Set Field [ RealValue::Y_exitA; Trim (RealValue::Y_exitA) ]
Set Field [ RealValue::Y_roach; Trim (RealValue::Y_roach) ]
Go to Record/Request/Page
[ Next; Exit after last ]
End Loop
Go to Layout [ original layout ]

The content of the imported file. Dots must be replaced with commas during import for the data to be correct.

This is how the data is displayed after importing from a file. As you can see, all numbers are merged without splitting. This is wrong, the diagrams are not displayed correctly.

 

This is an example of data, how everything should be displayed, the numbers where required are separated by commas, the percentages are similarly displayed in the top line as intended.

 

 

 

 

[Ocean West]

I would import the file and all the fields should be text then you can create shadow fields that sanitize the text data and then export these fields and or import them in to another table. 

 

[romankonis]

I don’t understand, can you explain your proposal in more detail and how to implement it with precision?

[Ocean West]

The issue here is the original file needs to be cleaned up. It starts as a CSV but doesn't have quotes around the fields, so it is harder to parse - also i have detected invisible charters in the original file, could be some conversion issue?

So in this example you drag the CSV file to a global field then the script will move the file to known location and then look at the contents of the file and perform some substitution on the data and write out the data to a known location to a .txt file then the data will be imported. 

The final part is to put the data in to your repeating fields what a few more substitutions to convert decimal to commas, and to strip any other characters that are not necessary.

data.fmp12.zip

 

[comment]

21 minutes ago, Ocean West said:

i have detected invisible charters in the original file,

Where is the original file? I don't see any attachments to the original post.

 

21 minutes ago, Ocean West said:

It starts as a CSV but doesn't have quotes around the fields

There is no need to have quotes around number fields. On the contrary, there is an advantage to not quoting numerical values.

 

On 11/14/2020 at 7:47 PM, romankonis said:

I need help of advanced users in the matter of converting characters, namely from period to comma.

I don't think it is necessary to convert periods to commas. If the source file is CSV, and the numerical values are not quoted (as shown in your screenshot), Filemaker will automatically convert the numerical values to the format of the target file - provided that the target fields are Number fields.

You may need to perform a post-import cleanup to (a) remove the non-numerical characters and (b) divide the percentage values by 100.

 

On 11/14/2020 at 7:47 PM, romankonis said:

This is an example of data, how everything should be displayed

This is quite confusing, because the display is pivoted relative to the source CSV. I doubt you really want that.

 

Edited November 16, 2020 by comment

[Ocean West]

The original file was sent to me via PM, i chose to reply here the same original file is in the container field. which include % symbol, and other characters.

[romankonis]

Thank you very much for your feedback!

After opening, I discovered 2 new scripts, and this puzzled me. I need a comma where the point is, and the fields contain data similar to the file, and the data from the table was converted into a chart. In the new file, which has attached Ocean West, after clicking the button, the data from the file is automatically imported into the table, but the commas are missing and I don’t understand how I can apply this in my database so that everything finally works. Guys, please be patient, I'm a beginner and I understand many things raw))) But I try))

18 minutes ago, comment said:

I don't think it is necessary to convert periods to commas. If the source file is CSV, and the numerical values are not quoted (as shown in your screenshot), Filemaker will automatically convert the numerical values to the format of the target file - provided that the target fields are Number fields.

You may need to perform a post-import cleanup to (a) remove the non-numerical characters and (b) divide the percentage values by 100.

The fields are numeric values.

19 minutes ago, comment said:

(a) remove the non-numerical characters and (b) divide the percentage values by 100.

How to implement this?

[comment]

The attached demo file imports the source CSV as is. Then it cleans up the data as explained in my previous post.

Note that the periods are automatically converted to commas. This is because the file is set to use comma as the decimal separator.

 

 

Demo.zip

Edited November 16, 2020 by comment

[romankonis]

Ok guys, thanks for your help, the boss showed me a ready-made script with a solution to the problem and everything works correctly. Thank you for your time. Another very important task has arisen, but I will write about it in a new topic. On the screenshot you can see what was added.

 

New challenge here - https://fmforums.com/topic/107275-filemaker-server-v19/

Edited November 19, 2020 by romankonis