Shopify CSV/Excel Export File Settings

Exportier - Data Export allows to configure the Shopify CSV/Excel export file, including encoding setup, data format option etc., based on user preferences.

You will find the File Settings option in the right section on the Report settings screen.

Click Edit to configure export file format and parameters.

Shopify Data Export File Type

Choose export file format between CSV and Excel.

CSV file doesn't have sheets. Therefore, the data from each tab will be appended one by one.

 

Shopify Data Export Filename format

Here you can customize the name format of the export files for a specific report with the predefined patterns.

By default we use the filename format based on the timestamp and report name: %Y-%M-%dT%H-%m-%s-%SZ_%R.%e

You can add a fixed string value, a date stamp, or a timestamp, or a report/store name etc. Advanced users can customize the set and the order of these patterns.

  • %Y - year

  • %M - month

  • %d - day

  • %H - hour

  • %m - minutes

  • %s - seconds

  • %S - milliseconds

  • %R - report name

  • %U - store name

  • %W - 5 random symbols

  • %e - file extension (csv / xlsx)

Be careful! If you omit a file extension, the export file may become unrecognizable by your operating system.

You can set the default filename format to be used for newly created reports on the Settings page.

 

 

 

 

Shopify CSV Export - Encoding, Delimiter and Quote Settings

These are the available options when using CSV file type:

  • Encoding

    • UTF-8 (Default)

    • ASCII

    • CP1252 (Windows-1252)

  • Delimiter

    • , (Default)

    • ;

    • Tab

  • Quote

    • " (Default)

    • '

 

Date format options

This is where you can control the date and time format used for exports. It is important that your dates be formatted correctly when importing data so you’ll want to choose whichever format is most convenient for your team to remember.

Timezone

Select the timezone in which the date will be displayed in exports. You can choose from UTC (+00:00) and the time zone of your Shopify account.

By default the time zone of your Shopify account will be used for your reports.

Date format

Date formatting depends on the selected file type. Excel stores dates as sequential numbers that are called serial values and doesn’t have native support of ISO8601 format and UTC offset.

You can customize date and time formatting in export files.

By default dates in exports are formatted in the following way:

  • CSV file type uses ISO8601 format (Example: 2021-04-06T03:39:41-04:00)

  • EXCEL file type uses format yyyy-MM-dd HH:mm:ss (Example: 2021-04-06 03:39:41)

 

If you want to use custom date formatting you need to select the “Custom” option and adjust the desired format.

 

 

 

Shopify CSV export custom date formatting

CSV uses pattern letters in the following table for date and timestamp formatting:

Symbol

Meaning

Presentation

Examples

G

era

text

AD; Anno Domini

y

year

year

2020; 20

D

day-of-year

number(3)

189

M/L

month-of-year

month

7; 07; Jul; July

d

day-of-month

number(3)

28

Q/q

quarter-of-year

number/text

3; 03; Q3; 3rd quarter

E

day-of-week

text

Tue; Tuesday

F

aligned day of week in month

number(1)

3

a

am-pm-of-day

am-pm

PM

h

clock-hour-of-am-pm (1-12)

number(2)

12

K

hour-of-am-pm (0-11)

number(2)

0

k

clock-hour-of-day (1-24)

number(2)

0

H

hour-of-day (0-23)

number(2)

0

m

minute-of-hour

number(2)

30

s

second-of-minute

number(2)

55

S

fraction-of-second

fraction

978

V

time-zone ID

zone-id

America/Los_Angeles; Z; -08:30

z

time-zone name

zone-name

Pacific Standard Time; PST

O

localized zone-offset

offset-O

GMT+8; GMT+08:00; UTC-08:00;

X

zone-offset ‘Z’ for zero

offset-X

Z; -08; -0830; -08:30; -083015; -08:30:15;

x

zone-offset

offset-x

+0000; -08; -0830; -08:30; -083015; -08:30:15;

Z

zone-offset

offset-Z

+0000; -0800; -08:00;

escape for text

delimiter

 

’‘

single quote

literal

[

optional section start

 

 

]

optional section end

 

 

The count of pattern letters determines the format.

Text: The text style is determined based on the number of pattern letters used. Less than 4 pattern letters will use the short text form, typically an abbreviation, e.g. day-of-week Monday might output “Mon”. Exactly 4 pattern letters will use the full text form, typically the full description, e.g, day-of-week Monday might output “Monday”. 5 or more letters will fail.

Number(n): The n here represents the maximum count of letters this type of datetime pattern can be used. If the count of letters is one, then the value is output using the minimum number of digits and without padding. Otherwise, the count of digits is used as the width of the output field, with the value zero-padded as necessary.

Number/Text: If the count of pattern letters is 3 or greater, use the Text rules above. Otherwise use the Number rules above.

Fraction: Use one or more (up to 9) contiguous 'S' characters, e,g SSSSSS, to format fraction of second. The fraction length would be padded to the number of contiguous ‘S’ with zeros.

Year: The count of letters determines the minimum field width below which padding is used. If the count of letters is two, then a reduced two digit form is used. For printing, this outputs the rightmost two digits. If the count of letters is less than four (but not two), then the sign is only output for negative years. Otherwise, the sign is output if the pad width is exceeded when ‘G’ is not present. 7 or more letters will fail.

Month: It follows the rule of Number/Text. The text form is depend on letters - ‘M’ denotes the ‘standard’ form, and ‘L’ is for ‘stand-alone’ form.

'M' or 'L': Month number in a year starting from 1. There is no difference between ‘M’ and ‘L’. Month from 1 to 9 are printed without padding.

'MM' or 'LL': Month number in a year starting from 1. Zero padding is added for month 1-9.

'MMM': Short textual representation in the standard form.

'LLL': Short textual representation in the stand-alone form. It should be used to format only months without any other date fields.

'MMMM': full textual month representation in the standard form. It is used for formatting months as a part of dates/timestamps.

'LLLL': full textual month representation in the stand-alone form. The pattern can be used to format only months.

am-pm: This outputs the am-pm-of-day. Pattern letter count must be 1.

Zone ID(V): This outputs the display the time-zone ID. Pattern letter count must be 2.

Zone names(z): This outputs the display textual name of the time-zone ID. If the count of letters is one, two or three, then the short name is output. If the count of letters is four, then the full name is output. Five or more letters will fail.

Offset X and x: This formats the offset based on the number of pattern letters. One letter outputs just the hour, such as ‘+01’, unless the minute is non-zero in which case the minute is also output, such as ‘+0130’. Two letters outputs the hour and minute, without a colon, such as ‘+0130’. Three letters outputs the hour and minute, with a colon, such as ‘+01:30’. Four letters outputs the hour and minute and optional second, without a colon, such as ‘+013015’. Five letters outputs the hour and minute and optional second, with a colon, such as ‘+01:30:15’. Six or more letters will fail. Pattern letter ‘X’ (upper case) will output ‘Z’ when the offset to be output would be zero, whereas pattern letter ‘x’ (lower case) will output ‘+00’, ‘+0000’, or ‘+00:00’.

Offset O: This formats the localized offset based on the number of pattern letters. One letter outputs the short form of the localized offset, which is localized offset text, such as ‘GMT’, with hour without leading zero, optional 2-digit minute and second if non-zero, and colon, for example ‘GMT+8’. Four letters outputs the full form, which is localized offset text, such as ‘GMT, with 2-digit hour and minute field, optional second field if non-zero, and colon, for example ‘GMT+08:00’. Any other count of letters will fail.

Offset Z: This formats the offset based on the number of pattern letters. One, two or three letters outputs the hour and minute, without a colon, such as ‘+0130’. The output will be ‘+0000’ when the offset is zero. Four letters outputs the full form of localized offset, equivalent to four letters of Offset-O. The output will be the corresponding localized offset text if the offset is zero. Five letters outputs the hour, minute, with optional second if non-zero, with colon. It outputs ‘Z’ if the offset is zero. Six or more letters will fail.

Optional section start and end: Use [] to define an optional section and maybe nested. During formatting, all valid data will be output even it is in the optional section. An optional section is started by [ and ended using ] (or at the end of the pattern).

Symbols of ‘E’, ‘F’, ‘q’ and ‘Q’ can only be used for datetime formatting, e.g. date_format.

 

Shopify EXCEL export custom date formatting

Excel uses pattern letters in the following tables for date and timestamp formatting:

Days, months, and years

To display

Use this code

Months as 1–12

m

Months as 01–12

mm

Months as Jan–Dec

mmm

Months as January–December

mmmm

Months as the first letter of the month

mmmmm

Days as 1–31

d

Days as 01–31

dd

Days as Sun–Sat

ddd

Days as Sunday–Saturday

dddd

Years as 00–99

yy

Years as 1900–9999

yyyy

Hours, minutes, and seconds

To display

Use this code

Hours as 0–23

h

Hours as 00–23

hh

Minutes as 0–59

m

Minutes as 00–59

mm

Seconds as 0–59

s

Seconds as 00–59

ss

Hours as 4 AM

h AM/PM

Time as 4:36 PM

h:mm AM/PM

Time as 4:36:03 P

h:mm:ss A/P

Elapsed time in hours; for example, 25.02

[h]:mm

Elapsed time in minutes; for example, 63:46

[mm]:ss

Elapsed time in seconds

[ss]

Fractions of a second

h:mm:ss.00

For example, the same date and time, say January 13, 2015 13:03, can be displayed in a various ways:

Format

Displays as

dd-mmm-yy

13-Jan-15

mm/dd/yyyy

01/13/2015

m/dd/yy

1/13/15

dddd, m/d/yy h:mm AM/PM

Tuesday, 1/13/15 1:03 PM

ddd, mmmm dd, yyyy hh:mm:ss

Tue, January 13, 2015 13:03:00

Follow this tutorial to format a date and time in Excel the way you want: https://support.microsoft.com/en-us/office/format-numbers-as-dates-or-times-418bd3fe-0577-47c8-8caa-b4d30c528309

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Misc options

Here you can customize miscellaneous report settings.

By default we use the long ID format and have currency columns included.