Jump to content

Converting symbols when importing data from a .txt file.


Recommended Posts

  • Newbies

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.

1.jpg

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.

2.jpg

 

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.

3.jpg

 

 

 

 

Link to post
Share on other sites

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. 

 

Link to post
Share on other sites

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

 

  • Like 1
Link to post
Share on other sites
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 by comment
Link to post
Share on other sites

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.

Link to post
Share on other sites
  • Newbies

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?

Link to post
Share on other sites

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 by comment
Link to post
Share on other sites
  • Newbies

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.

0101.jpg

 

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

Edited by romankonis
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.