Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Stan Denman is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    50

    Import data to MS Access from JotForms

    I am trying to figure out a way to import data to my MS Access database from information provided by my client via an online form. The problem I am having is the nature of the one-to-many aspect of MS Access and the fact that the export of jotform data is in csv format. So for example, my form asks the client to list their medications and details about each medication. Very annoying that jotforms solution is just to give you as many blanks as would be realistically possible, but I could not find much else out there. So, the result is a messy excel file with just one row that just has one medication and its detail after the other - no meaningful column names, just a mess. Any ideas?



    OK. So I have uploaded the excel file that JotForm gives me when I download. This is a given clients list of jobs she has done for the past 15 years. As you can see, all jobs have simply been strung on to the same row. I would imagine I need an excel file with a seperate row for each job in order for me to import into my MS Access DB that lists a given client's job in a "Jobs" table.

    I looks like this jotforms generated spreadsheet includes a number that designates each job in the description, like "Please List All Jobs in the Last 15 Years (STARTING WITH MOST RECENT JOB) >> 1 >> Approximate Start Date" where we are dealing with the first job.WorkHistoryRedacted.zip
    Last edited by Stan Denman; 07-22-2022 at 10:20 AM. Reason: Supply Excel Spreadsheet

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Post some of the data and any code you have.
    Also, a sample of what you want as end result would facilitate communications.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Do you get an email with the form data? Maybe my email parser might help you, it uses "keywords" as delimiters:
    http://forestbyte.com/ms-access-util...rser-importer/

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Stan Denman View Post
    ......the export of jotform data is in csv format. .......
    A csv file is just a text file. Excel may automatically open it, but you can open it using NotePad, NotePad++ or any TEXT editor.

    I have csv data files that are anywhere from 4000 to 24000 lines. I wrote VBA code to open the csv field, parse the data and insert the parsed data into the tables.

    As Orange requested, please post some data (change sensitive data) from and your dB. How proficient are you with VBA?

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    You can import a .csv file just using sql. But vba or sql we need to see what your file looks like in order to advise.

  6. #6
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Well spotted. OP seems to make a habit of it.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    Stan Denman is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    50
    Yes bravo to you both!! Clearly not the place to look for help.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you tried contacting Jotform for assistance?
    This just happened after 15 years?
    " This is a given clients list of jobs she has done for the past 15 years."

    Can you work with some sample data and research the pattern in order to generalize the csv to a format you need?

    You have had some suggestions--- but offered no comments.
    Last edited by orange; 07-24-2022 at 07:09 AM.

  10. #10
    Stan Denman is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    50
    15 years. What are you talking about?

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Yes bravo to you both!! Clearly not the place to look for help.
    suggest you take a look at this thread - there is an etiquette around cross posting that you should be aware of
    http://www.excelguru.ca/content.php?184

  12. #12
    Stan Denman is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    50
    The results of the online form completion are in a pdf file.

  13. #13
    Stan Denman is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    50
    K I get it. But so far the responses I have had centered on this grand infraction and, if may be so bold, are sprinkled with healthy dash of condensation, rather than the substance of my question. So yes I am done with the mean girls of Access here.

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you have been asked on this and at least one other forum to provide an example .csv file and details of the tables you want to import to. It's like you phoning your mechanic and saying 'my car won't work' and he responds with 'bring it in and we'll take a look' - nothing is going to happen until you take it in.

    I see you have now modified your original post to include a sample file - but didn't think to mention it. And still no sign of your table design and no comment on the suggestion made by Gicu or feedback on Orange's suggestion of talking to jotForm other than questioning what 15 years means (my guess - that is how long they have been in business - ergo this can't the the first time this has happened)

  15. #15
    Stan Denman is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    50
    Response from jotforms:

    Thanks for getting back to us. Unfortunately, since all data on the input table was included on the same form. It is considered as a single submission only; thus, creating a single row only on the Submission table.
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 12-11-2017, 01:36 PM
  2. Replies: 2
    Last Post: 01-12-2017, 10:35 AM
  3. Replies: 6
    Last Post: 07-31-2014, 12:53 PM
  4. Replies: 4
    Last Post: 06-20-2013, 12:19 PM
  5. Import data from PLC to MS Access database
    By lcv99 in forum Import/Export Data
    Replies: 5
    Last Post: 10-11-2012, 01:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums