Tableau读取制表符文件记录数与wc -l不符，求技术排查

Hey there! Since you're comfortable with Unix but new to Tableau, let's break down why you're seeing this huge split between wc -l's 127992 rows and Tableau's ~63k records. That nearly 2:1 ratio is a big clue—here are the most likely culprits and how to dig into them:

1. Fields Containing Line Breaks (The Top Suspect) #

wc -l counts every line break in the file, full stop. If any of your fields (like title or author) have embedded line breaks (e.g., a book title that wraps to a new line in the source data), one actual record will get counted as 2+ lines by wc -l. Tableau, though, reads tab-separated files properly—it treats the entire field (even with line breaks) as a single value, so it counts those as one record instead of multiple.

How to Verify: #

• First, check if every row has the correct number of fields (your header has 9 columns, so valid data rows should too):

  awk -F'\t' 'NF != 9 {print "Row " NR ": " $0}' file.csv
  

  This will spit out any rows that don't have 9 tab-separated fields. If you get a ton of results, that means line breaks in fields are splitting rows incorrectly in the count.

• To directly hunt for line breaks inside fields:

  awk -F'\t' '{for(i=1;i<=NF;i++) if($i ~ /\n/) print "Row " NR ", Field " i ": " $i}' file.csv
  

  This will show you exactly which fields in which rows have embedded newlines.

2. Line Ending Format Issues #

If your file was generated on Windows, it might use CRLF (\r\n) line endings instead of Unix's LF (\n). While wc -l handles this fine, Tableau sometimes has quirks with cross-platform line endings.

Quick Fix to Test: #

Convert the file to Unix-style line endings and reimport into Tableau:

dos2unix file.csv

(If you don't have dos2unix, you can use sed 's/\r$//' file.csv > fixed_file.csv instead.)

3. Accidental Filters or Data Type Mismatches in Tableau #

Since you're new to Tableau, it's easy to accidentally apply filters or have data type issues that hide records:

• Check for Filters: Go to your Data Source tab, look at the left-hand panel—if there's anything under "Filters", remove it temporarily and see if the record count jumps.
• Verify Data Types: If itemnumber is set to a "Number" type in Tableau, but some rows have non-numeric characters (even if your source DB shouldn't allow it, typos happen!), Tableau might drop those rows. Try changing itemnumber to a "String" type and reloading the data.
• Check for Invalid Rows: In Tableau's data preview, look for rows with red exclamation marks. Hover over them to see why Tableau rejected the record (e.g., missing required fields, invalid dates).

4. Count Valid Data Rows in Unix to Match Tableau #

Let's get a Unix count that matches what Tableau should see—exclude the header and only count rows with the correct number of fields:

awk -F'\t' 'NR > 1 && NF == 9 {count++} END {print "Valid data rows: " count}' file.csv

If this number is close to Tableau's 63756, that confirms wc -l was counting extra lines from embedded line breaks in fields.

内容的提问来源于stack exchange，提问作者Bubnoff