📊

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.

CSV Conversion Guides

Convert CSV to Excel When Data is in One Column

Open Excel and start Text Import Wizard

Select "Delimited" option

Choose the correct delimiter

Set text qualifier if needed

Preserve Leading Zeros When Converting CSV to Excel

Use Text Import Wizard

Set column data format to "Text"

Alternative: Add apostrophe prefix

How to Properly Choose Delimiter When Converting CSV

Examine the raw CSV file

Identify common delimiters

Check for delimiter conflicts

Test with preview

Convert CSV to JSON for Web Applications

Choose conversion method

Prepare CSV headers

Handle data types

Example Python script

Fix Encoding Errors When Opening CSV

Identify encoding issue

Try different encodings

Use proper import settings

Convert and save with UTF-8

Extract Table from PDF and Convert to CSV

Assess PDF table structure

Choose extraction tool

Extract using Tabula (recommended)

Clean extracted data

Convert CSV to SQL INSERT Scripts

Prepare CSV structure

Choose conversion method

Handle data types properly

Python script example

Related guides