Hi Bill,
Been there, done this kind of thing more than a few times. (targetted lists,
mailing lists, etc)
One way or another, you need to chop up that file into manageable chunks.
Way too big for Excel to handle, the problem is most likely a column shift
issue or a null or inappropriate data in a field that Access can't digest.
Sometimes (with large data list files) you find out that two (or several
more) formats have been concatenated into one file.
What I would do is chop that file into a bunch of chunks using primitive,
simple code in a loop. If you bust it into 30 more or less files, you will
have a little under 26k rows per file.
With 26k rows, if you have to, you can pop it into Excel and go down to the
row where it's breaking and try to figure it out, maybe do a repair/fix on
the row in Excel and then be able to import successfully. The trick here is
to find the record that was last successfully imported, then look at the
next row and figure out why Access doesn't like it. You can usually see
something different about it.
While breaking it down that far might seem to be a lot of extra work, it
isn't so bad. Only takes a little longer to import 30 x 26 than it does 1x
773 and the process is much easier to troubleshoot. You can get most of the
data imported and then deal with the remaining problem files.
NOTE: Keep track of which files you have imported, and which if any break on
you, you will need to come back to those files later. Sounds like common
sense but just in case you get over-confident, I warn you: don't rely on
remembering where you left off. Keeping notes, as you go along, saves a lot
of time in the long run (and it may save you from having to scrub a mess of
dupes from your database, too). Give you one guess regarding who learned
that lesson the hard way?
I would recommend importing a single file at a time into a temp table, scan
through the table, make sure it looks right, then if it looks good - move
the Chop_0??.csv file to a different folder, append the contents of the temp
table to the master table, then delete the contents of the temp table and
move on to the next file. Do NOT append files where the file broke and quit
importing mid-stream, just delete the partial import temp table contents and
move to the next file. When you have imported everything that will come in
smoothly, then it's time to start parsing through the problem children. This
knocks the task down into more manageable chunks and reduces the chance of
duping your data.
Here is the prep work.
Create a new folder in the current folder where the source (big CSV file)
is. Name that folder chopfile. This is where we will put the chopped up
pieces.
Insert the full path and name of your big csv file into the Const mySource
in the function, replacing the text
"C:\ReplaceThis\WithYour\PathAnd\Filename.csv"
Be sure to keep the quotes in place, you need those.
If you want to change the size of the files you can change the MaxCount
value - going down should be no problem as long as you keep it over 1000
because the file naming code will break at 999 files. Going upwards
somewhere around 32,768 (somewhere a bit below this) Excel stops being able
to load the file.
If you find a couple files have a lot of issues, you may want to chop them
into smaller chunks - once you have them down to 26k rows, you can load them
into Excel, cut and paste say 5,200 rows at a time into new worksheets, save
those as csv file using Excel. Keep whittling the job down - eventually you
get it conquered.
Create a code module, paste the function into the module - assign the
function to a macro and execute.
Here's the code to try... note if this fails make sure the Const mySource is
exactly right. That it is the full path and filename of your large csv file.
Make sure that you have created a folder in the folder where the csv file
is, named chopfile.
If that part is right but it still fails, probably my fault. I couldn't test
the code from this computer - it may have a typo or mangled concatenation of
a string. Send me a note with the error number and the description that is
coming up and I will try to fix it. Better cc me gllincoln (at) live (dot)
com because I may not be reading the forum for a couple days - I'm
travelling at the moment; doing a small programming job onsite in CA, plus
maybe a CRM training session or two (that isn't confirmed yet) over the next
week.
Public Function Chopper() As Boolean
On Error GoTo errHandler
Const mySource As String =
"C:\ReplaceThis\WithYour\PathAnd\Filename.csv"
Const MaxCount As Long = 26000
Dim Cycle As Integer
Dim fh As Integer
Dim ft As Integer
Dim myCount As Long
Dim s As String
Dim myChopName As String
Dim x As Integer
fs = FreeFile()
ft = FreeFile()
Open mySource For Input As #fs
myCount = 0
Cycle = 1
For x = Len(mySource) To 1 Step -1
If Mid(mySource, x, 1) = "\" Then
myChopName = Left(mySource, x) & "chopfile\Chop_"
x = 0: Exit For
End If
Next x
Open myChopName & Right("000" & Trim(CStr(Cycle)), 3) & ".csv" For
Output As #ft
Do While Not EOF(fs)
Line Input #fs, s
Print #ft, s
myCount = myCount + 1
If myCount = MaxCount Then
Close #ft
Cycle = Cycle + 1
Open myChopName & Right("000" & Trim(CStr(Cycle)), 3) & ".csv"
For Output As #ft
myCount = 0
End If
Loop
Close #ft
Close #fs
MsgBox "Chop completed: we have created " & Cycle & " files with " &
(Cycle * MaxCount) + myCount & " total rows of data.", vbInformation, "Guess
What Chopper, We Did It!"
Chopper = True
myExit:
Exit Function
errHandler:
MsgBox "Error: " & Err.Number & " - " & Err.Description, vbCritical,
"File Processing Error"
Chopper = False
End Function
Hope this helps,
Gordon
