Member Avatar for acakaliman

Hey all, I'm new-ish at MySQL and Navicat. I'm trying to import a file with about 1.16 million records and Navicat keeps "working" (I see the timer moving) but it actually stops processing records at 850,825 records. The log shows no errors whatsoever but I know there are many more records. Why is it not importing all records?

Here's the log:
[2011-09-21 09:37:24] [Msg] Import start
[2011-09-21 09:37:24] [Msg] Import type - Text file
[2011-09-21 09:37:24] [Msg] Import from - Y:\DistroPartner\EmailCampaign_ClassicActive.csv
[2011-09-21 09:37:24] [Msg] Create table [Classic_Active]
[2011-09-21 09:37:24] [Msg] Import table [Classic_Active]
[2011-09-21 09:42:41] [Msg] Processed:850825, Added:850825, Updated:0, Deleted:0, Errors:0
[2011-09-21 09:42:41] [Msg] Finished - Successfully]

I imported same file into MS Access and I get almost all records imported (42 have problems, 1,168,040 get created). I don't know how to figure this one out.

  • 4 Contributors
  • 4 Replies
  • 588 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by smantscheff

Dani AI

Generated

Practical, focused checklist and concrete commands to isolate why Navicat’s importer stops before the file end — builds on points from , and without repeating the thread log.

Quick integrity checks (file and encoding)

  • Confirm the CSV actually contains the expected number of lines and that the file is whole (mapped/network drives sometimes truncate or time out).
  • On Unix-like systems:
    wc -l big.csv
  • On Windows (cmd or PowerShell):
    find /v /c "" big.csv
Get-Content .\big.csv | Measure-Object -Line
  • Scan for embedded NULs or bad UTF-8, which commonly break GUI parsers:
    python - <<'PY'
with open('big.csv','rb') as f:
for i,line in enumerate(f,1):
  if b'\x00' in line: print('NUL at',i); break
  try: line.decode('utf-8')
  except UnicodeDecodeError: print('Bad UTF-8 at',i); break
PY

Reliable import via server/client (use these to compare)

  • Use the MySQL client and LOAD DATA so MySQL’s parser reports warnings/errors:
    mysql --local-infile=1 -u user -p dbname
mysql> LOAD DATA LOCAL INFILE 'C:/path/file.csv'
     INTO TABLE tablename
     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
mysql> SHOW WARNINGS;
mysql> SELECT COUNT(*) FROM tablename;
  • Or mysqlimport:
    mysqlimport --local --fields-terminated-by=',' --fields-enclosed-by='"' dbname /path/file.csv

Narrow the failing region

  • Split the file into chunks and import chunks until the stop point is reproducible (Unix split -l 100000 big.csv part_ or a small Python splitter). Once a small chunk reproduces the issue, inspect rows around the failure for quoting, embedded newlines, NULs, or encoding problems. If the console import succeeds but Navicat still stops, capture the smallest failing chunk and open a ticket with Navicat including exact steps and that chunk.

Recommended Answers

All 4 Replies

Member Avatar for debasisdas

Practically impossible to guess anything out of this.

Member Avatar for acakaliman

Practically impossible to guess anything out of this.

I was afraid of that. Would trying to import file "manually" (through console) work? Any suggestions on how to try other import methods to figure out what's going on?

Thanks a bunch.

Member Avatar for pritaeas

Did you contact Navicat support about this ?

Member Avatar for smantscheff

Use the console mysql client. It will tell you where it fails.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.