Plea for help #451, Importing MS Access

[Animl]

Ok,

What I am doing is building a weather program. Don't tell anyone it's never been done before and top secrete.

What I need to do is import an MS Access db of Zip codes into SQLite db. I've exported the MSA db to a deliminated txt\CVS file.

First lines of the txt\cvs db are;

"ZIP","LATITUDE","LONGITUDE","CITY","STATE","COUNTY","ZIP_CLASS"
"00501","+40.922326","-072.637078","HOLTSVILLE","NY","SUFFOLK","UNIQUE"
"00544","+40.922326","-072.637078","HOLTSVILLE","NY","SUFFOLK","UNIQUE"

I've DLed both sqlitebrowser-1.1-win and sqlitebrowser-1.3-win and have read some directions here, and re-read them, like 7 times. < Mostly because I realized I was reading spam add on how to make my <Not suitable for work> a full 3 inches bigger.

I create a db to save to
I set to import CVS (txt) and I set to get field names from first line.
Click create. <anxiously stares at monitor, sun crests the horizon, crickets start chirping>

No matter what software version I use it locks up in busy mode. Just in case I let it run because it is a big file, came back 25 mintues later and it's still hanging in busy mode. Until I click exit then I get a not responding message. I've tried this 10-15 times and my teeth are in the backyard to prove it. ;-)

Are there file size limits? I remember years ago programming with peanuts and acorns we couldn't import a txt file (db) bigger then 2mb. It would cut you off like a dog cutting off,... never mind...

Any ideas on what may be preventing me from importing this txt\cvs file to SQLite db?

Because I swear, I'm going to pick up a bottle,...and start drinking, and you can't stop me. And it will be all your faults <points around the room> for getting me addicted to this stuff. <tips hat>

<backs out of room slowly, closes door, re-opens door, shuts the light off, closes door slowly again, peeps in one more time, grabs bottle, closes door>

Ya Dern Code Pushers, evil I tell you, look what you've done to me!!!.

[Dermot]

How big is the CSV file?

It took me 2.5 hours to import 80,000 records. It may just be taking a long time.

[Animl]

How big is the CSV file?

It took me 2.5 hours to import 80,000 records. It may just be taking a long time.

It's 3 mb, so I should just leave it run then huh?

[JimS]

Have your loop update a label object, located on your project, with the zip code of the current record being added to your database. That way you can watch the progression. When the loop is completed, have your project play a loud sound file to alert you.
That should let you know if it is locking up, or just taking time.

[Animl]

I've already started it,.... 2.5 hours ago. So off to bed I go.

[TJ_Tigger]

Here are some functions from the programming in Lua first edition book. I wish I would have found these about a month ago. Might help if you want the import/export functions to be within your AMS project.

function toCSV (t)
      local s = ""
      for _,p in pairs(t) do
        s = s .. "," .. escapeCSV(p)
      end
      return string.sub(s, 2)      -- remove first comma
    end

  function escapeCSV (s)
      if string.find(s, '[,"]') then
        s = '"' .. string.gsub(s, '"', '""') .. '"'
      end
      return s
    end

function fromCSV (s)
      s = s .. ','        -- ending comma
      local t = {}        -- table to collect fields
      local fieldstart = 1
      repeat
        -- next field is quoted? (start with `"'?)
        if string.find(s, '^"', fieldstart) then
          local a, c
          local i  = fieldstart
          repeat
            -- find closing quote
            a, i, c = string.find(s, '"("?)', i+1)
          until c ~= '"'    -- quote not followed by quote?
          if not i then error('unmatched "') end
          local f = string.sub(s, fieldstart+1, i-1)
          table.insert(t, (string.gsub(f, '""', '"')))
          fieldstart = string.find(s, ',', i) + 1
        else                -- unquoted; find next comma
          local nexti = string.find(s, ',', fieldstart)
          table.insert(t, string.sub(s, fieldstart, nexti-1))
          fieldstart = nexti + 1
        end
      until fieldstart > string.len(s)
      return t
    end

[Animl]

Ok I let the software run for 8 hours and NO joy. Nothing. Neither version worked. There is no way I'm going to enter 10,000 enteries into the SQLite manually. LOL

Tried Intrigues script, I get a DB handle error.
Gonna have to give TJs script a try.

[Animl]

OK

Totally hung up in this. The software hangs, I don't know how to fire up the lua script., I can't get Intrigues script to work.

Lost

This is definatly something that should be placed into AMS. SQLite plugin is nice, but so far that I find if you want to fill it with a bunch of data,.... cya next year. ;-)

Anyone have any ideas?

[Dermot]

If you want to post the db I will try and import it for you. I have a few different tools I can try.

Attached is a CSV file with over 42,000 records. I can import this CSV in under 30 seconds.

Here is a link to an SQLite version of the file. It was too large to attach.
SQLite Zipcodes

[Animl]

If you want to post the db I will try and import it for you. I have a few different tools I can try.

Attached is a CSV file with over 42,000 records. I can import this CSV in under 30 seconds.

Here is a link to an SQLite version of the file. It was too large to attach.
SQLite Zipcodes

WOW thanks.

Looks like same info minus mail type which I don't need anyway.

But hey, if you don't mind if I use your DB I would sure appreciate it.

Kudos

Animl

[Dermot]

But hey, if you don't mind if I use your DB I would sure appreciate it.

No problem.