CSV Conversion Guides - Step-by-Step Solutions

📊

Beginner

⏱️ 5 minutes

Convert CSV to Excel When Data is in One Column

Fix common issues where CSV data appears as a single column in Excel due to delimiter problems.

▼

1

Open Excel and start Text Import Wizard

Go to Data → Get Data → From Text/CSV, or use Data → Text to Columns for existing data

2

Select "Delimited" option

Choose delimited instead of fixed width to specify separator characters

3

Choose the correct delimiter

Try comma, semicolon, tab, or custom delimiter. Preview shows how data will be separated

Common delimiters: , (comma) ; (semicolon) \t (tab) | (pipe)

4

Set text qualifier if needed

If data contains delimiter within text, set text qualifier to " (double quote)

Pro Tip: If data still appears in one column, try opening the CSV in Notepad first to identify the actual delimiter used.

🔢

Beginner

⏱️ 3 minutes

Preserve Leading Zeros When Converting CSV to Excel

Prevent Excel from automatically removing leading zeros from numbers like ZIP codes and phone numbers.

▼

1

Use Text Import Wizard

Import via Data → Get Data → From Text/CSV instead of double-clicking the file

2

Set column data format to "Text"

In the import preview, select columns with leading zeros and set format to "Text"

3

Alternative: Add apostrophe prefix

In CSV file, prefix numbers with apostrophe: '01234 becomes 01234 in Excel

"Name","ZIP Code"
"John Smith","'01234"
"Jane Doe","'90210"

Warning: Never open CSV files directly in Excel if preserving leading zeros is important - always use the import wizard.

🔗

Beginner

⏱️ 4 minutes

How to Properly Choose Delimiter When Converting CSV

Learn to identify and select the correct delimiter for clean data separation.

▼

1

Examine the raw CSV file

Open CSV in text editor (Notepad, VS Code) to see actual separators used

2

Identify common delimiters

Look for patterns between data fields

Comma: Name,Age,City
Semicolon: Name;Age;City  
Tab: Name	Age	City
Pipe: Name|Age|City

3

Check for delimiter conflicts

Ensure delimiter doesn't appear within data fields

4

Test with preview

Use import preview to verify correct column separation

Best Practice: European systems often use semicolon (;) as delimiter due to comma being decimal separator in many locales.

🔗

Intermediate

⏱️ 8 minutes

Convert CSV to JSON for Web Applications

Transform tabular CSV data into JSON format for API consumption and web development.

▼

1

Choose conversion method

Online tool (Convertio, JSON-CSV), programming script (Python, JavaScript), or specialized software

2

Prepare CSV headers

Ensure first row contains clean column names (these become JSON keys)

Good: name,age,city
Bad: Name (with spaces),Age!!!,City/Location

3

Handle data types

Specify if values should be strings, numbers, or booleans in JSON output

4

Example Python script

import pandas as pd
df = pd.read_csv('data.csv')
json_output = df.to_json(orient='records', indent=2)
with open('output.json', 'w') as f:
    f.write(json_output)

Pro Tip: Use 'records' orientation for array of objects, 'index' for object with row indices as keys.

🔤

Intermediate

⏱️ 6 minutes

Fix Encoding Errors When Opening CSV

Resolve character encoding issues that cause gibberish text and special character problems.

▼

1

Identify encoding issue

Look for symptoms: strange characters (Ã, â, ¿), question marks, boxes

2

Try different encodings

Common encodings to test: UTF-8, UTF-8 BOM, Windows-1252, ISO-8859-1

3

Use proper import settings

In Excel: Data → Get Data → specify encoding. In Notepad++: Encoding menu

4

Convert and save with UTF-8

Once opened correctly, save as UTF-8 to prevent future issues

# Python fix example
import pandas as pd
df = pd.read_csv('file.csv', encoding='latin-1')
df.to_csv('fixed.csv', encoding='utf-8', index=False)

Common Issue: Windows Excel often creates CSV with Windows-1252 encoding, causing problems on other systems.

📄

Advanced

⏱️ 15 minutes

Extract Table from PDF and Convert to CSV

Advanced technique to extract tabular data from PDF documents and convert to CSV format.

▼

1

Assess PDF table structure

Determine if table has clear borders, consistent formatting, and good text quality

2

Choose extraction tool

Options: Tabula (free), Adobe Acrobat, online tools (SmallPDF, ILovePDF), or Python libraries

3

Extract using Tabula (recommended)

Upload PDF → Select table area → Choose output format (CSV) → Download

4

Clean extracted data

Remove extra spaces, fix merged cells, verify column alignment

# Python cleanup example
import pandas as pd
df = pd.read_csv('extracted.csv')
df = df.dropna(how='all')  # Remove empty rows
df.columns = df.columns.str.strip()  # Clean headers

Limitation: OCR accuracy depends on PDF quality. Scanned documents may require additional text recognition processing.

🗃️

Intermediate

⏱️ 10 minutes

Convert CSV to SQL INSERT Scripts

Generate SQL INSERT statements from CSV data for database population and migration.

▼

1

Prepare CSV structure

Ensure headers match target database column names exactly

2

Choose conversion method

Online tools (CSV to SQL converter), database tools, or custom scripts

3

Handle data types properly

Quote text values, format dates, handle NULL values

INSERT INTO users (name, age, email) VALUES ('John Smith', 25, '[email protected]'), ('Jane Doe', 30, '[email protected]');

4

Python script example

import pandas as pd
df = pd.read_csv('data.csv')
with open('insert.sql', 'w') as f:
    for _, row in df.iterrows():
        values = "', '".join(str(v) for v in row.values)
        f.write(f"INSERT INTO table_name VALUES ('{values}');\n")

Best Practice: Test with a small sample first and consider using batch INSERT statements for better performance.