Data Type Conversion
A practical ETL guide to converting strings, numbers, dates, booleans, JSON, XML, and binary values into target-ready data types.
Data type conversion changes values from one data type to another during ETL so the target system receives usable, compatible data. It turns text into integers, decimals, booleans, dates, timestamps, and other target-ready values without changing the business meaning. When it goes wrong, the import still may run. It just runs like a spreadsheet wearing a fake moustache.
What Is Data Type Conversion?
Data type conversion is the ETL process of changing a value from one technical type into another while preserving the value's intended meaning. A source value may arrive as text, but the target may need an integer, decimal, boolean, date, timestamp, binary value, or string with a specific format.
In practice, data type conversion sits between extraction and loading. The workflow extracts source data from CSV, Excel, JSON, XML, SQL Server, Oracle, PostgreSQL, Access, APIs, or flat files. It profiles the source values, validates whether they can be converted, applies the conversion rule, logs failures, then loads accepted rows into the target.
This is one part of the wider data transformation process. It is deliberately narrower than the pillar topic. Data type conversion focuses on value representation. It does not decide the full target model, which belongs to schema transformation. It does not define source-to-target field relationships, which belongs to data mapping. It does not handle deep calendar rules, which belong to date transformations.
The rule of thumb is simple: if the field stays the same business field but changes technical type, you are doing data type conversion. If the table structure changes, you are probably doing schema transformation. If the field moves to a different target field, you are doing mapping. If the date needs time zones, financial periods, or date arithmetic, go to the date guide before the calendar starts looking at you suspiciously.
Data Type Conversion Matters Because Databases Have Standards
Data type conversion improves data quality because it proves values are fit for the target. A text value of `"42"` may be a valid integer. A text value of `"forty-two"` is not. Good ETL makes that distinction before the database driver has a dramatic episode.
It also ensures database compatibility. SQL Server, Oracle, PostgreSQL, MySQL, Access, flat files, JSON documents, and XML feeds do not always represent types in the same way. Oracle `NUMBER`, SQL Server `BIT`, PostgreSQL `numeric`, Access yes/no fields, and JSON booleans need deliberate handling. Hope is not a data type, despite how many imports appear to depend on it.
System integration depends on type conversion too. A CRM may export `"Y"` and `"N"` as text. The ERP may expect `1` and `0`. A reporting warehouse may expect a true boolean. The values all describe the same business fact, but the technical representation must match the target contract.
Conversion reduces import errors. It catches invalid values, overflow, precision loss, null conflicts, bad encodings, and incompatible source types before loading. It also simplifies reporting. Analysts should not have to cast text amounts to decimals in every dashboard. Do it once in ETL, document the rule, and let the report query behave like it has had a proper breakfast.
Calculations also need correct types. A price stored as text cannot be reliably summed, averaged, rounded, or compared. A date stored as text cannot be filtered safely across locales. Preparing data for analytics starts by turning source values into trustworthy target types.
For the broader design view, the Transformation hub connects type conversion with standardization, cleansing, aggregation, mapping, and other ETL transformation patterns.
Common Data Type Conversions Follow Repeatable ETL Patterns
Most data conversion examples are not exotic. They are ordinary source values arriving in the wrong technical shape. The important work is not memorising syntax. It is deciding what is valid, what should fail, and how to prove the target received the correct type.
String to Integer
Source type: Text such as `"1042"` or `" 1042 "`.
Target type: Whole number column such as SQL Server `INT` or PostgreSQL `integer`.
When to use it: Use it for IDs, counts, quantities, status codes, and whole-number reference values supplied in CSV, Excel, JSON, or XML.
Common pitfalls: Thousands separators, spaces, decimals, signs, blank strings, and values outside the target range.
ETL example: A CSV order file sends `Quantity` as text. ETL trims the value, validates digits only, converts `"12"` to integer `12`, and rejects `"12 boxes"`.
String to Decimal
Source type: Text such as `"1250.75"`, `"1,250.75"`, or `"1.250,75"`.
Target type: Decimal or numeric column with defined precision and scale.
When to use it: Use it for prices, balances, weights, tax values, exchange rates, and measurements.
Common pitfalls: Locale separators, currency symbols, rounding, scale mismatch, and scientific notation.
ETL example: An Excel supplier sheet sends `UnitPrice` as text. ETL removes the currency symbol, applies the agreed locale, converts to `DECIMAL(12,2)`, and logs rows with too many decimal places.
String to Boolean
Source type: Text values such as `"Y"`, `"N"`, `"true"`, `"false"`, `"1"`, `"0"`, `"Yes"`, or `"No"`.
Target type: Boolean, bit, or logical flag.
When to use it: Use it for active flags, opt-in fields, approved indicators, and deleted markers.
Common pitfalls: Unapproved labels such as `"TBC"`, mixed languages, blank values, and systems that store booleans as integers.
ETL example: A CRM export sends `IsActive = Yes`. ETL uses a lookup table so `Yes` becomes `true`, `No` becomes `false`, and anything else goes to an exception file.
String to Date
Source type: Text that represents a calendar date.
Target type: Date column without a time component.
When to use it: Use it when a target database needs a true date type instead of a display string.
Common pitfalls: Ambiguous locale, impossible dates, default dates, and mixed source patterns.
ETL example: A CSV file sends `InvoiceDate = 2026-07-20`. ETL validates the allowed pattern and converts the value to a date column. Detailed calendar rules belong in the separate date transformations guide.
String to Timestamp
Source type: Text containing date and time, sometimes with fractional seconds or an offset.
Target type: Timestamp, datetime, or datetimeoffset column.
When to use it: Use it for event times, audit fields, API updates, and change tracking.
Common pitfalls: Missing time zones, incompatible precision, local server assumptions, and truncated fractional seconds.
ETL example: A JSON API sends `updated_at` as text. ETL parses the timestamp, checks the precision the warehouse supports, and writes the target timestamp field.
Integer to String
Source type: Whole number such as `1042`.
Target type: Text field or file output column.
When to use it: Use it for fixed-width exports, concatenated keys, file formats, and systems that store identifiers as text.
Common pitfalls: Lost leading zeros, padding rules, and accidental numeric formatting.
ETL example: A warehouse customer number `1042` becomes text `CUST001042` for a legacy XML export.
Decimal to String
Source type: Decimal value such as `1250.50`.
Target type: Formatted text.
When to use it: Use it when a downstream file, XML document, JSON payload, or report needs a specific text representation.
Common pitfalls: Rounding, trailing zeros, locale separators, and currency symbols applied inconsistently.
ETL example: A finance export formats decimal `1250.5` as `1250.50` before sending an XML invoice file.
Date to String
Source type: Database date value.
Target type: Text in a required output format.
When to use it: Use it for filenames, flat files, XML attributes, API payloads, and legacy imports.
Common pitfalls: Using display settings instead of explicit format rules.
ETL example: A date value becomes text `20260720` for a nightly export filename. The date formatting rule stays documented outside the mapping sheet.
Timestamp to Date
Source type: Timestamp or datetime value.
Target type: Date-only value.
When to use it: Use it when reporting only needs the calendar day, not the exact event time.
Common pitfalls: Losing time zone context, truncating before conversion, and hiding event ordering.
ETL example: A support ticket timestamp becomes `created_date` for daily reporting, while the original timestamp remains available for audit.
Date to Timestamp
Source type: Date-only value.
Target type: Timestamp or datetime value.
When to use it: Use it when a target column requires time even though the source only knows the date.
Common pitfalls: Silent default times and incorrect assumption that midnight has business meaning.
ETL example: An invoice date becomes `2026-07-20 00:00:00` because the target column is datetime. The workflow marks the time as generated.
Boolean to Integer
Source type: Boolean value such as `true` or `false`.
Target type: Integer, bit, or numeric flag.
When to use it: Use it for databases and files that represent flags as `1` and `0`.
Common pitfalls: Different systems use `-1` for true, especially older Microsoft and Access-style systems.
ETL example: A clean `is_active = true` field becomes integer `1` for an old import format.
Integer to Boolean
Source type: Integer value such as `1`, `0`, or sometimes `-1`.
Target type: Boolean or bit value.
When to use it: Use it when importing legacy flags into modern databases or applications.
Common pitfalls: Unexpected flag values such as `2`, blank, or `-1`.
ETL example: An Access migration converts `-1` to `true` and `0` to `false`, with all other values rejected for review.
Binary to Text
Source type: Binary data, encoded bytes, or blob values.
Target type: Text using a known character encoding.
When to use it: Use it for EBCDIC, UTF-8, Windows-1252, Base64 payloads, or file extracts from older systems.
Common pitfalls: Wrong encoding, control characters, corrupted bytes, and binary values that are not actually text.
ETL example: A mainframe extract stores customer notes as encoded bytes. ETL decodes them using the approved encoding and rejects rows with invalid byte sequences.
JSON to String
Source type: JSON object, array, or scalar value.
Target type: String column or file field.
When to use it: Use it when storing raw payloads, creating audit columns, or passing JSON to a target that expects text.
Common pitfalls: Double encoding, lost escaping, very long strings, and treating JSON conversion as schema design.
ETL example: An API response is archived as a JSON string before selected fields are loaded into relational tables.
XML to String
Source type: XML document, node, or fragment.
Target type: String or large text field.
When to use it: Use it for raw XML archives, message queues, audit trails, or document exports.
Common pitfalls: Character encoding, namespace handling, escaping, and text fields too short for the document.
ETL example: A supplier XML invoice is stored as text for audit while key values are converted and loaded into invoice tables.
Notice the repeated pattern: validate first, convert second, load third. If conversion fails, log the raw value. Do not replace bad values with tidy nonsense unless the business has approved that exact rule. A wrong value with neat formatting is still wrong. It has simply put on a tie.
Data Type Conversion Techniques Have Different Tradeoffs
There are several ways to convert data types in ETL. The best choice depends on where the data is staged, how repeatable the process is, how much validation is needed, and who must maintain the workflow later.
| Technique | Advantages | Disadvantages |
|---|---|---|
| SQL CAST | Portable enough for simple database conversions and clear when staging data already sits in a table. | Syntax and behaviour vary. Failed casts may stop the whole statement unless validation is done first. |
| SQL CONVERT | Useful in SQL Server workflows where style codes control date and string formats. | Less portable across databases and easy to hide business rules inside database-specific scripts. |
| ETL transformation components | Visible, repeatable, and easier for mixed teams to review. Good for scheduled file imports and database loads. | Still needs clear rules. Drag-and-drop does not magically understand that `Fred` is not a valid decimal. |
| Expressions | Good for conditional casts, trimming, rounding, null handling, and fallback values in one workflow step. | Long expressions become hard to maintain unless named, tested, and documented. |
| Validation before conversion | Protects the load by separating valid, invalid, missing, and ambiguous values before casting. | Adds setup work, although it usually saves time after the first bad supplier file arrives. |
| Lookup tables | Excellent for converting codes, labels, boolean values, and legacy flags into target-approved values. | Reference data needs ownership. A stale lookup table is still stale, just with better formatting. |
| Python scripting | Useful for awkward parsing, special encodings, unusual APIs, and rare conversion rules. | Adds code, dependencies, testing, and support overhead. Scripts should earn their keep. |
| Business rules | Keeps conversion tied to real meaning, especially for flags, default values, rounding, and exception handling. | Business rules change. If nobody owns them, the workflow becomes folklore with a progress bar. |
| AI-assisted workflows | Helpful for suggesting candidate conversion rules, explaining source samples, and reducing repetitive setup. | Needs human review. AI can be confident and wrong, which is also true of several project meetings. |
SQL Server CAST and CONVERT are useful when data is already staged in SQL Server. PostgreSQL casts and numeric types are documented in the PostgreSQL data types guide. Oracle type behaviour is covered in the Oracle Database documentation. ETL still needs the surrounding checks, logs, and rejection rules.
Built-in ETL transformations are usually the safest default for recurring imports. They make the conversion visible in the workflow and reduce the chance of business logic disappearing into a long script. SQL is excellent for set-based conversion in staging tables. Python is useful for unusual encodings or rare source formats. AI-assisted workflows help draft rules, but they should never be the final judge. Trust, but verify. Especially when the robot sounds calm.
Before-and-After Examples Show Successful and Failed Conversions
Conversion design improves when teams test both good and bad values. A sample containing only perfect rows tells you very little. It is the data equivalent of a car passing its MOT while parked in the showroom.
| Source value | Source type | Target type | Converted value | Handling strategy |
|---|---|---|---|---|
| `"42"` | String | Integer | `42` | Trim, validate digits, convert. |
| `"1,250.50"` | String | Decimal(12,2) | `1250.50` | Apply approved locale, remove separator, check scale. |
| `"Y"` | String | Boolean | `true` | Lookup approved flag values. |
| `2026-07-20 09:15:30` | Timestamp | Date | `2026-07-20` | Derive reporting date and keep original timestamp. |
| `true` | Boolean | Integer | `1` | Convert using the target system's flag convention. |
| Source value | Attempted target | Why it fails | Recommended handling |
|---|---|---|---|
| `"12 boxes"` | Integer | Contains text after the number. | Reject or split quantity and unit by explicit rule. |
| `"999999999999"` | Small integer | Value exceeds target range. | Reject, widen target type, or confirm the source field meaning. |
| `"1.234,56"` | Decimal | Locale is ambiguous without a source rule. | Apply source-specific locale or route to review. |
| `"Maybe"` | Boolean | Not an approved true or false value. | Reject unless the business defines a third state. |
| Binary bytes | Text | Encoding is unknown or invalid. | Decode with the documented character set and log invalid byte sequences. |
The failed table is where the value is. It forces the team to decide what should happen before the scheduled job meets production data. Better ten failed sample rows today than ten million cheerful mistakes tomorrow.
Real ETL Examples Make Data Type Conversion Concrete
Importing CSV files into SQL Server
A supplier CSV stores every column as text. The ETL workflow stages the raw values, trims whitespace, converts quantities to integers, prices to decimals, flags to bit values, and dates to SQL Server date columns. Bad rows are written to an exception file instead of stopping the whole load.
Loading Excel spreadsheets into PostgreSQL
An Excel workbook contains numeric-looking text, blank cells, and one column that mixes `Y`, `N`, and `Unknown`. ETL profiles the worksheet, converts approved values to PostgreSQL types, and leaves the original spreadsheet untouched for audit.
Converting Oracle NUMBER values
Oracle `NUMBER` columns may map to integer, decimal, or high-precision numeric fields depending on precision and scale. ETL checks values before loading SQL Server, PostgreSQL, or MySQL so finance totals are not rounded into modern art.
Processing JSON APIs
A REST API returns booleans, numbers, nested objects, and timestamp strings. ETL stores the raw JSON payload, converts selected scalar values to database types, and routes unexpected arrays or nulls to review.
Converting timestamps for reporting
Operational systems store event timestamps, but a reporting table needs both `created_at` and `created_date`. ETL casts the timestamp to a date for grouping while keeping the original timestamp for ordering and audit.
Migrating Access databases
Access often stores true as `-1`, false as `0`, and dates in ways that make other databases raise an eyebrow. ETL converts flags, dates, memo fields, and numeric values explicitly before loading a modern target.
Preparing data warehouses
A warehouse load converts source strings into numeric measures, integer keys, boolean flags, and typed dates in staging. Facts and dimensions then load with predictable types instead of report-specific fixes.
Exporting data to XML
A target XML format expects all values as text with exact decimal places and date formats. ETL converts database values to strings at the export edge, not inside the source tables.
Useful implementation references include the Advanced ETL Processor tutorials, Excel to database tutorial, Excel to SQL Server import guide, CSV to JSON tutorial, JSON to SQL Server import guide, XML to SQL Server import guide, Oracle export article, SQL Server export article, and PostgreSQL export article.
For text cleanup before conversion, see the string transformations overview. Trimming spaces, removing symbols, and normalizing case often happen before a string can become a number or flag.
Common Data Type Conversion Challenges Start With Bad Assumptions
Invalid values break otherwise sensible rules
Invalid values are the obvious problem. A text field intended for decimals may contain `N/A`, `TBC`, `unknown`, comments, or formulas copied from a spreadsheet. A customer once had an import fail because one date column contained `N/A`. One cell. That is all it takes. Data can be very economical with its chaos.
Overflow means the target type is too small
Overflow happens when a valid-looking number is too large for the target. An order quantity may fit an integer but not a small integer. A source identifier may exceed a target numeric field because it should have been text all along. Check ranges before loading.
Precision loss is expensive in finance data
Precision loss is common with decimals. Converting `NUMBER`, `FLOAT`, `money`, or text amounts into a target decimal requires explicit precision and scale. Finance teams notice rounding errors. They notice them with the calm intensity of a cat watching a laser pointer.
Null handling needs an agreed rule
NULL handling needs agreement. Empty string, blank cell, missing JSON property, XML empty node, and database null are not always the same thing. Decide which values become null and which fail validation.
Locale and encoding rules change the meaning
Locale differences affect decimals, dates, booleans, and text. `1,234` may mean one thousand two hundred thirty-four or one point two three four. Character encodings add another problem. UTF-8, Windows-1252, EBCDIC, and binary blobs need explicit decoding rules.
Database types rarely map perfectly
Timestamp precision can break loads when a source supports nanoseconds and a target stores milliseconds or seconds. Incompatible database types also matter. Oracle `NUMBER`, SQL Server `uniqueidentifier`, PostgreSQL arrays, JSON fields, XML fields, and Access yes/no values rarely map perfectly without rules.
Best Practices Keep Data Type Conversion Safe in Production
- Keep the original source value when audit, troubleshooting, or reconciliation matters.
- Define the target data type, length, precision, scale, null rule, and default before conversion.
- Validate source values before casting them into target types.
- Reject or quarantine invalid values instead of silently forcing defaults.
- Document locale rules for decimal separators, thousands separators, dates, and text encoding.
- Set explicit rounding rules for decimal conversions.
- Check integer ranges before loading smaller target types.
- Preserve leading zeros when identifiers are really text, not numbers.
- Treat boolean conversion as a business rule when source values are labels or codes.
- Use staging tables for messy files, spreadsheets, APIs, and legacy exports.
- Log conversion failures with the source row, source field, raw value, and reason.
- Test with blank strings, nulls, overflow values, invalid dates, high precision decimals, and unexpected encodings.
- Do not convert types inside every report if the ETL layer can do it once and document it.
- Review conversion rules with both technical owners and business owners before scheduling the workflow.
The practical rule is this: conversion should make the value more reliable, not merely more acceptable to the target database. If the workflow has to lie to make the load pass, the design is not finished.
Data Type Conversion Is Not the Same as Data Standardization
Data standardization makes values consistent. Data type conversion changes their technical type. They often work together, but they answer different questions.
| Question | Data Type Conversion | Data Standardization |
|---|---|---|
| Main purpose | Change a value from one type to another, such as string to decimal. | Make values follow a consistent format, label, casing, or code set. |
| Example | Convert `"1250.50"` text into decimal `1250.50`. | Standardize `UK`, `U.K.`, and `United Kingdom` to one approved value. |
| ETL risk | Invalid casts, overflow, precision loss, null conflicts. | Wrong reference values, inconsistent labels, mixed formatting. |
| Related guide | Transformation hub | Data Standardization |
Data Type Conversion Is Broader Than Date Transformations
Date transformations include some type conversion, but they go further into calendar meaning. This article only covers date values when the main task is changing technical type.
| Question | Data Type Conversion | Date Transformations |
|---|---|---|
| Main purpose | Convert values between technical types. | Parse, format, calculate, validate, and normalize date and time values. |
| Example | Convert string `"2026-07-20"` to a date column. | Convert local timestamps to UTC and derive reporting periods. |
| Primary risks | Cast failure, null handling, target compatibility. | Locale ambiguity, time zones, daylight saving, month-end rules. |
| Use this guide when | The field changes type but the calendar rule is simple. | The workflow needs date-specific business logic. |
Data Type Conversion Is Smaller Than Schema Transformation
Schema transformation changes structure. Data type conversion may be one step inside it, but it is not the whole structural design.
| Question | Data Type Conversion | Schema Transformation |
|---|---|---|
| Main purpose | Change value representation between compatible types. | Change tables, columns, keys, relationships, hierarchy, or file layout. |
| Example | Convert Oracle `NUMBER(12,2)` to PostgreSQL `numeric(12,2)`. | Split a flat CSV into customer, order, and order line tables. |
| Output | Same business field, different technical type. | Different structure or model shape. |
| Related guide | What is Data Transformation? | Schema Transformation |
Data mapping is separate again. Mapping says where a value goes. Type conversion says how its type changes on the way. Keep those rules separate unless you enjoy debugging by archaeology.
A Practical Checklist Catches Conversion Problems Early
Use this checklist before scheduling a recurring ETL workflow. It is less exciting than discovering an overflow in month-end reporting, which is exactly the point.
- List every source field that needs a type change.
- Record the source type as supplied, not as you wish it had been supplied.
- Define the target type, length, precision, scale, null behaviour, and default value.
- Profile sample values for blanks, symbols, separators, ranges, and mixed formats.
- Decide which invalid values are rejected, defaulted, corrected, or routed for review.
- Create validation rules before the conversion step.
- Keep raw values in staging when the process is recurring or auditable.
- Run before-and-after tests with both successful and failed conversions.
- Compare row counts, rejected rows, totals, and key counts after conversion.
- Schedule the workflow only after logging and exception handling have been tested.
After conversion, run validation checks for required fields, ranges, duplicates, referential integrity, and reconciliation totals. The Data Validation hub covers those checks in more detail.
Frequently Asked Questions
What is data type conversion?
Data type conversion changes a value from one data type to another while preserving its meaning. In ETL, it commonly converts text to numbers, text to dates, numbers to strings, booleans to integers, JSON to strings, XML to strings, and legacy flags to modern target types.
What is data type conversion in ETL?
Data type conversion in ETL happens after extraction and before loading. The workflow reads source values, validates them, converts accepted values to the target type, and routes values that cannot be trusted.
Is data type conversion the same as type casting?
They are closely related. Type casting is a common implementation method for data type conversion, especially in SQL, but ETL data type conversion also includes validation, routing, logging, null handling, and business rules.
What is the difference between datatype conversion and data casting?
Datatype conversion describes the broader process of changing value representation between data types. Data casting usually refers to the operation that performs the change, such as casting text to integer.
Why do CSV imports need data type conversion?
CSV files do not carry strong data types. Most values arrive as text, so ETL must convert quantities, amounts, dates, booleans, and codes before loading typed database columns.
How should ETL handle failed conversions?
Failed conversions should be logged with the row number, field name, raw value, and reason. In production workflows, route failed rows to an exception table or file instead of silently replacing them.
Should identifiers be converted from string to integer?
Only when they are true numbers. If an identifier has leading zeros, prefixes, or formatting rules, keep it as text. A customer code of `00123` is not the same as integer `123` to a legacy system.
How do you convert string values to decimals safely?
Validate the allowed characters, apply the correct locale, remove approved currency symbols, check precision and scale, then convert. Reject values that would overflow or require unapproved rounding.
How do you convert strings to booleans in ETL?
Use an approved lookup or rule set, such as `Y`, `Yes`, and `1` becoming true, and `N`, `No`, and `0` becoming false. Route unknown labels to review.
Is string to date conversion covered by data type conversion?
Yes, but only at the type level. Detailed calendar rules, time zones, date arithmetic, and reporting periods belong in date transformations.
Is data type conversion the same as schema transformation?
No. Data type conversion changes values between types. Schema transformation changes structure, including tables, columns, keys, relationships, and nested layouts.
Is data type conversion the same as data mapping?
No. Data mapping defines where fields go from source to target. Data type conversion defines how a source value changes type before it is loaded.
Can Advanced ETL Processor automate data type conversion?
Yes. Advanced ETL Processor supports built-in conversion transformations, expressions, SQL, Python, validation, workflow automation, and AI workflows for repeatable ETL data type conversion.
When should you not use ETL software for data type conversion?
If you have a one-off file with a few rows, a spreadsheet formula may be enough. Use ETL software when the conversion repeats, affects production data, needs logs, or must run without manual repair.
Automate Data Type Conversion in Advanced ETL Processor
Advanced ETL Processor automates type conversion with built-in transformations, expressions, SQL, Python, validation, and AI-assisted workflows.
If the conversion is a one-off, use a spreadsheet. If it repeats every day, download the 30-day fully functional trial and automate it.
Convert the types before the database converts your evening into support work.