7 Replies Latest reply on Jun 18, 2012 4:29 AM by user14040

    Importing CSV File Question

    user14040

      Title

      Importing CSV File Question

      Post

      I am trying to import a CSV file into a solution

      The CSV file has 40 cells of data but I want it to go into one record looks like ever new cell letter is a new record.

       

      Example:

       

      Cell J1 through J40 

      I would like it to go into a existing  record 

      ( use a import script to import the CSV file into fields J1 through J40)

      Can this be done? If so how?

       

      Thanks

       

        • 1. Re: Importing CSV File Question

          My standard suggestion after 20+ years of importing is this:

          Do not import new data directly into your working file, use a temporary file.

          Open Filemaker Pro. Select Open File and locate the file in question, you might have to select csv as the format.

          If the first row of data is field names you can select the option to use that row as the field names.

          Let Filemaker do the importing and then inspect the results.

          You can delete all of the columns(fields) you aren't interested in. And rename the fields in this file to match the field names you will import into. This is easier than moving the fields up and down the import thing since you just select Matching Names.

          You can clean up the data in this new import file as needed and most important make sure it is what you want.

          When done, import this file into your working file.

          This method also has the wonderful resource of being able to creat a copy as you make changes, revert to the last copy if you mess up, and not make a mess of your working database.

          You can open a new form view in both databases and inspect the fields and rename etc.

          • 2. Re: Importing CSV File Question
            user14040

            Thanks for your help,

             

            when I import the CSV file to create a new database it shows each cell line as a record. Maybe what I am trying to do cant be done.  Because the 40 records are correct but they are not new records they are field data for one record.

             

            I dont know how to import 1-40 records into the 1-40 fields of one record and it does not take forever to do.

             

            Thanks

            • 3. Re: Importing CSV File Question
              TSGal

              Larry Williams:

              Thank you for your posts.

              When importing from a table, each column is a field and each row is a record.  Therefore, rows 1 through 40 will go into 40 different records.  Therefore, you may want to import the data into a temporary file, and then use a loop to go through each record and update the appropriate record and field in the main table.

              Let me know if you need additional clarification.

              TSGal
              FileMaker, Inc.

              • 4. Re: Importing CSV File Question
                user14040
                What would the script look like I know a little about scripting but still learning Thanks
                • 5. Re: Importing CSV File Question
                  harringg

                  Not knowing how the rest of your CVS data looks, is there a reason you can't transpose the data in Excel before import?

                  J1

                  J2

                  J3

                  ...

                  J40

                  Copy (J1:J40), New Sheet, Paste Special>Transpose 

                  CELL: A1 | B1 | C1... | AN1

                  DATA: J1,J2,J3...,J40

                  • 6. Re: Importing CSV File Question

                    OK, a bit more information...

                    It seems you have a file that contains data for one record with the data for each field in the record appearing on a new line in the text file.

                    Mr.
                    John
                    Smith
                    123 Street
                    City
                    State

                    You say this is a csv file but it is not. A csv file would be:

                    "Mr.","John","Smith",etc... 

                    The data is wrapped with "", but not necessarily, but it is delimited by commas.

                    The simplest idea is to replace all of the carriage returns with tabs or commas and then import that file.

                    Replace with commas would produce

                    Mr.,John,Smith....

                    and with tabs would produce

                    Mr.JohnSmith123 street...etc.

                    Now the interesting point here is that the above would have solved the problem in 10 seconds...

                    Now if you have a thousand similar files with only one record in each..., I would suggest a different answer involving importing each into a text field and parsing it with a short script.

                    • 7. Re: Importing CSV File Question
                      user14040

                      Thanks for your help I will try some of the ideals here if I have anymore questions i will post.

                       

                      Thanks again